SQL transpose full table

Question

I need to do the following transpose in MS SQL

from:

Day  A  B 
---------
Mon  1  2
Tue  3  4
Wed  5  6
Thu  7  8
Fri  9  0

To the following:

Value Mon Tue Wed Thu Fri 
--------------------------
A      1   3   5   7   9
B      2   4   6   8   0

I understand how to do it with PIVOT when there is only one column (A) but I can not figure out how to do it when there are multiple columns to transpose (A,B,...)

Example code to be transposed:

select LEFT(datename(dw,datetime),3) as DateWeek, 
  sum(ACalls) as A, 
  Sum(BCalls) as B 
from DataTable
group by LEFT(datename(dw,datetime),3)

Table Structure:

Column DataType
DateTime Datetime
ACalls int
BCalls int

Any help will be much appreciated.

Possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — tom redfern, Oct 14 '16 at 10:42

Taryn · Accepted Answer · 2013-03-08T16:18:45.163

30

In order to transpose the data into the result that you want, you will need to use both the UNPIVOT and the PIVOT functions.

The UNPIVOT function takes the A and B columns and converts the results into rows. Then you will use the PIVOT function to transform the day values into columns:

select *
from
(
  select day, col, value
  from yourtable
  unpivot
  (
    value
    for col in (A, B)
  ) unpiv
) src
pivot
(
  max(value)
  for day in (Mon, Tue, Wed, Thu, Fri)
) piv

See SQL Fiddle with Demo.

If you are using SQL Server 2008+, then you can use CROSS APPLY with VALUES to unpivot the data. You code would be changed to the following:

select *
from
(
  select day, col, value
  from yourtable
  cross apply
  (
    values ('A', A),('B', B)
  ) c (col, value)
) src
pivot
(
  max(value)
  for day in (Mon, Tue, Wed, Thu, Fri)
) piv

See SQL Fiddle with Demo.

Edit #1, applying your current query into the above solution you will use something similar to this:

select *
from
(
  select LEFT(datename(dw,datetime),3) as DateWeek,
    col, 
    value
  from DataTable 
  cross apply 
  (
    values ('A', ACalls), ('B', BCalls)
  ) c (col, value)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv

edited Mar 08 '13 at 16:18

answered Mar 08 '13 at 15:48

Taryn

242,637
56
362
405

Thanks for the quick answer. I can not manage to do the unpivot for the following query: select LEFT(datename(dw,datetime),3) as DateWeek, SUM(ACalls) as A, SUM(BCalls) as B from _online_interval_data group by LEFT(datename(dw,datetime),3) Unpivot (=Incorrect syntax near the keyword 'Unpivot') – Selrac Mar 08 '13 at 15:58
I'm using SQL2008 R2. Same problem error with Cross apply --> select LEFT(datename(dw,datetime),3) as DateWeek, SUM(ACalls) as A, SUM(BCalls) as B from data group by LEFT(datename(dw,datetime),3) cross apply (=Incorrect syntax near the keyword 'cross') – Selrac Mar 08 '13 at 16:05
@user2148939 I don't understand that code that you posted. Please edit your original post with your table structure and then the code you are using. – Taryn Mar 08 '13 at 16:07
@user2148939 See my edit on how you will incorporate your current query into the solution. – Taryn Mar 08 '13 at 16:19
Yes, yes!! Thanks you!! I guess if you need more columns you then need to add more values values ('A', ACalls), ('B', BCalls), ('C', DCalls),... Correct? – Selrac Mar 08 '13 at 16:32
@user2148939 Yes, that is correct. You will just add more columns in the `VALUES`. :) – Taryn Mar 08 '13 at 16:32
1

The column 'col' seems to get ordered alplabetcially. How can you change the order descending like C, B, A or other order like A, C, B? – Selrac Mar 08 '13 at 23:13
You will just add an ORDER BY col DESC at the end of the SQL. After the ) piv – Taryn Mar 08 '13 at 23:21
1

But what about if you want another order. For example, if 'col' are the names of the months. You don't want them ordered like April, August, December,... You want them like January, February, March,... How can you keep this order? – Selrac Mar 08 '13 at 23:36
@Selrac Please post a new question with more details about the issue. It will be easier than going back and forth in the comments. :) – Taryn Mar 09 '13 at 14:31

SQL transpose full table

1 Answers1

Linked

Related