SQL Server SQL transpose full table

luaexgnf  于 11个月前  发布在  其他
关注(0)|答案(2)|浏览(174)

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.

ktca8awb

ktca8awb1#

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
a2mppw5e

a2mppw5e2#

with orig as
(
select 'Mon' as day_,   1 as A,  2 as B from dual union all 
select 'Tue',  3,  4 from dual union all 
select 'Wed',  5,  6 from dual union all 
select 'Thu',  7,  8 from dual union all 
select 'Fri',  9,  0 from dual union all 
select 'Sat',  10,  1 from dual union all 
select 'Sun',  11,  5 from dual 
)
, chg1 as
(
select col_, day_, case when col_='A' then A else B end  AorB
from orig cross join (select 'A' col_ from dual union all select 'B' col_ from dual)
)
  
select 
   col_
 , max(case when day_='Mon' then AorB else null end) Mon
 , max(case when day_='Tue' then AorB else null end) Tue
 , max(case when day_='Wed' then AorB else null end) Wed
 , max(case when day_='Thu' then AorB else null end) Thu
 , max(case when day_='Fri' then AorB else null end) Fri
 , max(case when day_='Sat' then AorB else null end) Sat
 , max(case when day_='Sun' then AorB else null end) Sun
from chg1
group by col_

相关问题