How to convert columns to rows in sql server

Question

Please consider this table:

ID         Page          Line           C01          C02          C03        
---------------------------------------------------------------------
1          122            11             1            0            1
1          123            11             1            1            1
1          124            12             0            0            0
1          125            16             1            0            1
1          127            11             0            1            0

I want to convert this table to this one:

ID         Page          Line           City         Value
-----------------------------------------------------------
1          122            11            C01            1           
1          122            11            C02            0  
1          122            11            C03            1  
1          123            11            C01            1  
1          123            11            C02            1  
1          123            11            C03            1  
...

How I can do this in appropriate way?

Solution 1

Use UNPIVOT. Try something like:

SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
   (Value FOR City IN 
      (C01, C02, C03)
)AS unpvt;

Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)

Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Solution 2

Pivot & UnPivot will solve the issue :)

follow the links:

• http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
• http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Solution 3

Below query should do your requirement.

SELECT ID, PAGE, LINE, "C01", C01 FROM TABLE
UNION 
SELECT ID, PAGE, LINE, "C02", C02 FROM TABLE
UNION
SELECT ID, PAGE, LINE, "C03", C03 FROM TABLE

Solution 4

You can achieve this by doing something like this in Linq to sql

Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?

View more solutions