Skip to content
Advertisement

Transpose rows to columns using multiple pivots

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

01 02 .. 51 52
VAL9 0 0 .. 65.36% 65.66%
VAL4 0 0 .. 31.83% 26.16%
VAL5 0 0 .. 2.69% 7.80%
VAL7 0 0 .. 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)  
)

Advertisement

Answer

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

Advertisement