This article will helps to convert row values to column/fields, either name or headers using PIVOT operator.

Getting Started

Sometimes we need to generate reports which shows row values as column headers or name. Normally developers use a CASE WHEN statement,

do a cursor or for-loop and generate tables on runtime, or use UNION/UNION ALL etc.. Another way will do the logic on code-behind page.

All these different work rounds can be avoid by using "PIVOT Transformation". Consider a sample: I want to list the Top Contributors of C# Corner.

11.png

The output will look like below.

22.png

Now, to convert all usernames to columns or fields and show their respective points below. The expected output should looks like below:

33.png

There are many different ways available on the web. But using PIVOT is much simpler and faster.

The below following method used is PIVOT with Dynamic Columns to convert rows to columns.

44.png


Query Explained

STUFF function inserts a string to another string.

e.g.:

  1. SELECT STUFF('Test', 1, 0, '*')
and the output will be *Test.

For XML Path is used for concatenating the Strings.From above query, select @columns columns will return output as following..

55.png

Relational operator PIVOT definition from msdn:

"PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output."

Conclusion

Hope this article helps you to convert rows values to columns headers. I will update the article with more complex samples soon.

Download the attachment and test the query. Post comments and rate it. Also, post all the doubts to the forum section.

Thank You !