Transpose rows to columns using multiple pivots

Question

I am trying to transpose rows to columns without any luck . What I have now

WEEK_NUMBER	VAL9	VAL4	VAL5	VAL7
45	63.27%	28.58%	7.92%	0.23%
52	65.66%	26.16%	7.80%	0.38%
50	90.97%	8.45%	0.29%	0.29%
46	82.49%	12.43%	3.01%	2.06%
47	66.33%	29.53%	3.77%	0.36%
51	65.36%	31.83%	2.69%	0.11%
49	61.88%	28.29%	9.46%	0.37%
44	45.34%	45.55%	8.45%	0.66%
48	43.75%	40.01%	16.01%	0.23%

this is based on the following query

select 
     to_char(for_date,'ww') as week_number
    ,val9
    ,val4
    ,val5
    ,val7
from data_tab
where to_char(for_date,'yy') = '21'

I am trying to get following result

	..	51	52
VAL9	..	65.36%	65.66%
VAL4	..	31.83%	26.16%
VAL5	..	2.69%	7.80%
VAL7	..	0.11%	0.38%

I have tried by doing next query but with no luck . If someone can just point me in the right direction I would appreciate.

select * from (
select 
         to_char(for_date,'ww') as week_number
        ,val9
        ,val4
        ,val5
        ,val7
    from data_tab
    where to_char(for_date,'yy') = '21')
pivot (
    min(val9)--, min(val4), min(val5), min(val7) 
        for week_number in (01 ,02 ,03 ,04 ,05 ,06 ,07 ,08 ,09 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,38 ,39 ,40 ,41 ,42 ,43 ,44 ,45 ,46 ,47 ,48 ,49 ,50 ,51 ,52 ,53)  
)

Please, describe the issue you have with your current code. "With no luck" is not very descriptive — astentx
– astentx, Commented Jan 6, 2022 at 15:18

MT0 · Accepted Answer · 2022-01-06 15:19:07Z

UNPIVOT and then PIVOT:

SELECT *
FROM   (
  SELECT to_char(for_date,'ww') as week_number
       , val9
       , val4
       , val5
       , val7
  FROM   data_tab
  WHERE  for_date >= DATE '2021-01-01'
  AND    for_date <  DATE '2022-01-01'
  -- WHERE EXTRACT(YEAR FROM for_date) = 2021
)
UNPIVOT (
  value FOR key IN (val9, val4, val5, val7)
)
PIVOT (
  MAX(value) FOR week_number IN (
    '01' AS "01",
    '02' AS "02",
    -- ...
    '44' AS "44",
    '45' AS "45",
    '46' AS "46",
    '47' AS "47",
    '48' AS "48",
    '49' AS "49",
    '50' AS "50",
    '51' AS "51",
    '52' AS "52",
    '53' AS "53"
  )
)

Which, for the sample data:

CREATE TABLE data_tab (for_date, val9, val4, val5, val7) AS
SELECT DATE '2021-11-13', 63.27, 28.58,  7.92,  0.23 FROM DUAL UNION ALL
SELECT DATE '2021-12-31', 65.66, 26.16,  7.80,  0.38 FROM DUAL UNION ALL
SELECT DATE '2021-12-17', 90.97,  8.45,  0.29,  0.29 FROM DUAL UNION ALL
SELECT DATE '2021-11-20', 82.49, 12.43,  3.01,  2.06 FROM DUAL UNION ALL
SELECT DATE '2021-11-27', 66.33, 29.53,  3.77,  0.36 FROM DUAL UNION ALL
SELECT DATE '2021-12-24', 65.36, 31.83,  2.69,  0.11 FROM DUAL UNION ALL
SELECT DATE '2021-12-10', 61.88, 28.29,  9.46,  0.37 FROM DUAL UNION ALL
SELECT DATE '2021-11-06', 45.34, 45.55,  8.45,  0.66 FROM DUAL UNION ALL
SELECT DATE '2021-12-03', 43.75, 40.01, 16.01,  0.23 FROM DUAL;

Outputs:

KEY 01 02 44 45 46 47 48 49 50 51 52 53

VAL9 null null null 45.34 63.27 82.49 66.33 43.75 61.88 90.97 65.36 65.66

VAL4 null null null 45.55 28.58 12.43 29.53 40.01 28.29 8.45 31.83 26.16

VAL5 null null null 8.45 7.92 3.01 3.77 16.01 9.46 .29 2.69 7.8

VAL7 null null null .66 .23 2.06 .36 .23 .37 .29 .11 .38

db<>fiddle here

Thank you very much . I understand what I was missing here !

Collectives™ on Stack Overflow

Transpose rows to columns using multiple pivots

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

KEY	01	02	44	45	46	47	48	49	50	51	52	53
VAL9	null	null	null	45.34	63.27	82.49	66.33	43.75	61.88	90.97	65.36	65.66
VAL4	null	null	null	45.55	28.58	12.43	29.53	40.01	28.29	8.45	31.83	26.16
VAL5	null	null	null	8.45	7.92	3.01	3.77	16.01	9.46	.29	2.69	7.8
VAL7	null	null	null	.66	.23	2.06	.36	.23	.37	.29	.11	.38

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related