Rotate SQL Table

Question

I have a table like below

Code        Attribute        Value
--------------------------------------
USA         Population       300M
USA         Language         US English
CANADA      Population       30M
USA         Capital          DC
CANADA      Language         CA English

How can I rotate table for 90 degrees and make a view like below to run query and sp on it?

Code        Language        Population       Capital
------------------------------------------------------
USA         US English         300M              DC
CANADA      CA English          30M              NULL

The term for rotating a table 90 degrees is called a `PIVOT`. — mellamokb, Jul 23 '12 at 22:07
see this question: [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — YetAnotherUser, Jul 23 '12 at 22:08

score 10 · Accepted Answer · answered Jul 23 '12 at 22:09

You could use PIVOT (for SQL Server 2005+):

SELECT *
FROM YourTable AS T
PIVOT (MIN(Value) FOR Attribute IN ([Language],[Population],[Capital]) AS PT

For all version of SQL Server you can use this:

SELECT  Code, 
        MIN(CASE WHEN Attribute = 'Language' THEN Value END) [Language],
        MIN(CASE WHEN Attribute = 'Population' THEN Value END) [Population],
        MIN(CASE WHEN Attribute = 'Capital' THEN Value END) [Capital]
FROM YourTable
GROUP BY Code

Rotate SQL Table

1 Answers1