How to create pivot tables?

[Rusli yusuf]

Hi All,

How to convert rows to columns

the table is structured like:

NO_ID DATE_TRANS SHF
------ ---------- ---
080003 2010-08-21 11
080003 2010-08-22 99
080003 2010-08-23 88
080004 2010-08-21 11
080004 2010-08-22 99
080004 2010-08-23 88

This is the result I want to see :

NO_ID DATE_TRANS21 DATE_TRANS22 DATE_TRANS23
------ ------------ ------------ ------------
080003 11 99 88
080004 11 99 88

can you help me


Regard,
Rusli

[Anand Kaushal]

You could do it like this
SELECT NO_ID,
(SELECT B.SHF FROM TABLE B WHERE B.DATE_TRANS = '2010-08-21') DATE_TRANS_21,
(SELECT B.SHF FROM TABLE B WHERE B.DATE_TRANS = '2010-08-22') DATE_TRANS_22,
(SELECT B.SHF FROM TABLE B WHERE B.DATE_TRANS = '2010-08-23') DATE_TRANS_23
FROM TABLE A
GROUP BY NO_ID

or

SELECT NO_ID,
MAX((CASE WHEN DATE_TRANS = '2010-08-21' THEN SHF ELSE 0 END)) DATE_TRANS_21,
MAX((CASE WHEN DATE_TRANS = '2010-08-22' THEN SHF ELSE 0 END)) DATE_TRANS_22,
MAX((CASE WHEN DATE_TRANS = '2010-08-23' THEN SHF ELSE 0 END)) DATE_TRANS_23,
FROM TABLE
GROUP BY NO_ID


This is the general format for pivoting data.

In your case i'm assuming you'd want to generate this for different dates hence specifying the dates won't work. Let's say you apply the filters and all, you could still pivot it from least to max date as follows:

consider c1 and no_id, c2 as date and c3 as shf..

with temp(c1, c2, c3) as
(values (1,2, 11),(1,3,23),(1,4,45),(2,2,13),(2,3,132),(2,4,32)
),
temp2 as (
select row_number() over(partition by c1 order by c2) r1,c1, c2, c3
from temp
)
select o.c1,min(c2) start,
(select i.c3 from temp2 i where i.r1 = 1 and o.c1 = i.c1) val1,
(select i.c3 from temp2 i where i.r1 = 2 and o.c1 = i.c1) val2,
(select i.c3 from temp2 i where i.r1 = 3 and o.c1 = i.c1) val3
from temp2 o
group by c1


This would ofcourse work if there is a record for each date for each no_id.. if there's any missing, the values won't be displayed in the proper column.

But i hope you've gotten the basic idea of how data can be transposed. if you want to solve the problem of missing dates, then you could take a temp3 table with all distinct dates across all no_ids and then left join your table with temp3 so you'll be guaranteed a row for each..