CODEWITHSUNDEEP
sqlunpivot
Meaghan
Meaghan

Reputation: 19

SQL Unpivot Example

Right now I have the following table and I want to unpivot it so my columns and rows are flipped like below - thank you!

HAVE

current

WANT

want

Upvotes: 0

Views: 9428

Answers (1)

Cameron S
Cameron S

Reputation: 79

This example is in Transact-SQL. The Oracle (11g+) syntax is similar but with a few more options, such as how to treat null values.

-- setting up a table variable with your starting data
declare @t table 
    (  Name         varchar(10) primary key 
      ,[Age 18-24]  int
      ,[Age 25-34]  int
      ,[Age 35-44]  int  );

insert @t (Name, [Age 18-24], [Age 25-34], [Age 35-44])
values   ('John', 1, 0, 0 )
        ,('Maria', 0, 0, 1 )
        ,('June' , 0, 0, 1 )

--- the actual query starts here 

select [Name], [Column], [Value]
from 
    (   -- select relevant columns from source 
        select Name, [Age 18-24], [Age 25-34], [Age 35-44]
        from @t
    ) as piv
UNPIVOT
    (   -- define new result columns.  
        ----  [Value] is the detailed count/sum/whatever
        ----  [Column] get the old name of the column 

        [Value] 
        for [Column] in ([Age 18-24], [Age 25-34], [Age 35-44]) 
    ) as unpiv

Upvotes: 3

Related Questions