Transpose rows into columns in a result SQL

Question

I have the following table:

User    Week    OT      DW
----------------------------
A       1       28.0    54
A       2       19.5    46
A       3       10.0    38
A       4       11.0    32
B       1       25.0    54
B       2       17.5    46
B       3       10.0    40
B       4       12.0    40

I want to have the following result:

User    Week1_OT    Week2_OT    Week3_OT    Week4_OT    Week1_DW    Week2_DW    Week3_DW    Week4_DW
----------------------------------------------------------------------------------------------------
A       28.0        19.5        10.0        11.0        54          46          38          32
B       25.0        17.5        10.0        12.0        54          46          40          40

Does anyone have an idea how to do that? Thank you.

PIVOT or CASE statements....

Mitch Wheat
– Mitch Wheat

2020-11-30 04:34:12 +00:00
Commented Nov 30, 2020 at 4:34 — Mitch Wheat
– Mitch Wheat, Commented Nov 30, 2020 at 4:34
@MitchWheat best performance, because the data quite large.

Jml
– Jml

2020-11-30 04:37:54 +00:00
Commented Nov 30, 2020 at 4:37 — Jml
– Jml, Commented Nov 30, 2020 at 4:37

Tim Biegeleisen · Accepted Answer · 2020-11-30 04:37:22Z

2

Aggregate by user and use pivoting logic to generate the week columns:

SELECT
    User,
    MAX(CASE WHEN Week = 1 THEN OT END) AS Week1_OT,
    MAX(CASE WHEN Week = 2 THEN OT END) AS Week2_OT,
    MAX(CASE WHEN Week = 3 THEN OT END) AS Week3_OT,
    MAX(CASE WHEN Week = 4 THEN OT END) AS Week4_OT,
    MAX(CASE WHEN Week = 1 THEN DW END) AS Week1_DW,
    MAX(CASE WHEN Week = 2 THEN DW END) AS Week2_DW,
    MAX(CASE WHEN Week = 3 THEN DW END) AS Week3_DW,
    MAX(CASE WHEN Week = 4 THEN DW END) AS Week4_DW
FROM yourTable
GROUP BY
    User;

answered Nov 30, 2020 at 4:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Steephen · Accepted Answer · 2020-11-30 06:01:03Z

0

DECLARE @Temp table(_Userid CHAR(10),Week1_OT DECIMAL(12,2),Week2_OT DECIMAL(12,2),Week3_OT DECIMAL(12,2),Week4_OT DECIMAL(12,2));
DECLARE @Temp1 table(_Userid CHAR(10),Week1_Dw DECIMAL(12,2),Week2_Dw DECIMAL(12,2),Week3_Dw DECIMAL(12,2),Week4_Dw DECIMAL(12,2));

Insert into @Temp(_Userid,Week1_OT,Week2_OT,Week3_OT,Week4_OT)
select *
from 
(
  select UserId,Weekdy,OT
  from #temp
) src
pivot
(
  sum(OT)
  for weekdy in ([1], [2], [3],[4])
) piv
;


Insert into @Temp1(_Userid,Week1_DW,Week2_DW,Week3_DW,Week4_DW)
select *
from 
(
  select UserId,Weekdy,DW
  from #temp
) src
pivot
(
  sum(DW)
  for weekdy in ([1], [2], [3],[4])
) piv
;

select * from @Temp t1 Join @Temp1 t2 on t1._Userid = t2._Userid

edited Nov 30, 2020 at 6:01

Steephen

16.1k7 gold badges46 silver badges47 bronze badges

answered Nov 30, 2020 at 4:55

Sridhar

111 bronze badge

Comments

Dale K · Accepted Answer · 2020-12-10 03:11:41Z

0

Try:

;with cte
as(
select * from (select week,OT from yourtable where User='A') as t  
pivot (max(OT) for week in (1,2,3,4,)) as p)
,cte2 as
(select * from (select week,OT from yourtable where User='B') as t  
pivot (max(OT) for week in (1,2,3,4,)) as p)

select * from cte
union all
select * from cte2

edited Dec 10, 2020 at 3:11

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 8, 2020 at 9:16

Shine

264 bronze badges

Collectives™ on Stack Overflow

Transpose rows into columns in a result SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related