Search code examples
sqlsql-serverpivotunpivot

SQL transpose full table


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.


Solution

  • 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