Making rows in to colums using sql query

Question

I have a temp table in which have one coloum with four rows.

Table
------
vaibhav
IBM
12
'T'

I need a temp table

Col1     Col2    Col3     Col4
------   -----  -----    ------ 
Vaibhav  IBM      12       'T'

Do anyone have any idea.

Answer 1

I believe you are looking for the Pivot clause, straight from msdn ...

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. 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. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

see msdn article for more.

Answer 2

The problem with the standard PIVOT statement is that you need to know the column values before hand so you can define them in the "FOR xxx IN ('x','y','x'...)" statement.

In order to get around this you'll need to do some dynamic SQL and create a string that contains the columns.

An interesting article on this is here:

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

I have built many PIVOT queries using this technique and despite the fact that dynamic SQL can't be optimized as well as stored procedures that are hard coded, they work well.

If needed I can post some example code.