Janna Sherazi - 1 year ago 47

SQL Question

I would like to transpose the following SQL result:

`A``sum ``week_no`

`a ``1 ``22 `

`a ``2 ``24 `

into table like this:

`A``week_22``week_23``week_24`

`a ``1 ``0 ``2 `

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)

Answer Source

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
```