SQL Server transform rows into columns

Question

I'm working on TSQL stored procedure and I need to transform temporary table columns into rows. Currently I'm using updating table one by one but I think using pivot and unpivot this could be achieved this easily.

Data Source:

Periods      Stat1       Stat2       Stat3      Stat4
--------------------------------------------------------
Value1       1.011       1.012       1.013       1.014
Value2       1.011       1.021       1.031       1.041
Value3       1.011       2.211       1.311       1.411

Expected Output:

Stats        Value1       Value2       Value3
-----------------------------------------------
Stat1         1.011       1.011       1.011      
Stat2         1.012       1.021       1.211 
Stat3         1.013       1.031       1.311 
Stat4         1.014       1.041       1.411

Really appreciate any help on this ?

I have checked simple-talk.com/sql/t-sql-programming/… it contains some solution but still i couldn't match to this one. — LittleOne
– LittleOne, Commented May 30, 2013 at 8:29

Ian Preston · Accepted Answer · 2013-05-30 08:52:04Z

1

As you've indicated it's a process of unpivoting then pivoting the data:

with statTable as
(
select periods = 'Value1', Stat1 = 1.011, Stat2 = 1.012, Stat3 = 1.013, Stat4 = 1.014
union all select 'Value2', 1.011, 1.021, 1.031, 1.041
union all select 'Value3', 1.011, 2.211, 1.311, 1.411
)
, up as
(
  select periods,
    c.[Stats], 
    c.value
  from statTable
  cross apply
  (
    values ('Stat1', Stat1), ('Stat2', Stat2), ('Stat3', Stat3), ('Stat4', Stat4)
  ) c ([Stats], value)
)
select [Stats],
  Value1,
  Value2,
  Value3
from up
pivot
(
  sum(value)
  for periods in (Value1, Value2, Value3)
) p

SQL Fiddle with demo.

If you're not using SQL Server 2008 or above, you can use UNPIVOT instead of CROSS APPLY:

with statTable as
(
select periods = 'Value1', Stat1 = 1.011, Stat2 = 1.012, Stat3 = 1.013, Stat4 = 1.014
union all select 'Value2', 1.011, 1.021, 1.031, 1.041
union all select 'Value3', 1.011, 2.211, 1.311, 1.411
)
, up as
(
  select periods,
    up.[Stats], 
    up.value
  from statTable
  unpivot
  (
    value
    for [Stats] in (Stat1, Stat2, Stat3, Stat4)
  ) up
)
select [Stats],
  Value1,
  Value2,
  Value3
from up
pivot
(
  sum(value)
  for periods in (Value1, Value2, Value3)
) p

SQL Fiddle with demo.

edited May 30, 2013 at 8:52

answered May 30, 2013 at 8:43

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LittleOne Over a year ago

Thanks Ian. Seems this should work for my problem. I'll check.

Collectives™ on Stack Overflow

SQL Server transform rows into columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related