SQL data over week_no into columns

Question

I would like to transpose the following SQL result:

Asum  week_no
a 1      22
a 2      24

into table like this:

Aweek_22week_23week_24
a 1&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp0 &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 2 &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp

How could I achieve this on Oracle? Thanks! (I am supposed to consider weekly data over many years, so building cases is not an option)

If you don't have a fixed set of column names in advance, then this is a SQL anti-pattern. Transpose / Pivot your data in your application's presentation layer, not the database. — MatBailie, Jun 27 '16 at 12:37
My problem is that the software I am currently on - Exasol, does not support pivot function and the only way around they suggest is the multiple CASE WHEN scenario. — Janna Sherazi, Jun 27 '16 at 12:45
http://oraclecoder.com/tutorials/three-ways-to-transpose-rows-into-columns-in-oracle-sql--160 — Thorarins, Jun 27 '16 at 12:52

score 1 · Accepted Answer · answered Jun 27 '16 at 13:10

Mostly I have assumed this query on your sample data where it will fill the gaps of week no and Pivot the result set

Select A,[22] [week_22],[23] [week_23],[24] [week_24] from (
Select A,Sum,Weekno from Table 
UNION
Select * from ( 
SELECT  top 1 t1.A,'' AS Sum,t1.Weekno-1 AS Weekno
FROM     Table t1
LEFT OUTER JOIN 
         Table  t2 
ON       t2.Weekno=t1.Weekno-1 
WHERE    t2.Weekno IS NULL 
AND      t1.Weekno > 0     
ORDER BY t1.Weekno desc )T)TT
PIVOT (MAX(SUM) FOR WEEKNO IN ([22],[23],[24]))PVT

SQL data over week_no into columns

1 Answers1