column to row conversion of data in netezza

Question

I have data into below format in source table :

     MTH_ID    VOL

   201306   29699420633
   201307   31573662296
   201308   31482365216
   201309   31037480379
   201310   33828084122

I want to write a sql query in netezza to convert data into below format

201306          201307      201308       201309       201310

29699420633   31573662296   31482365216   31037480379    33828084122

In source table I have more than 50 rows and above data is just sample data. Please help me on this. Thanks in advance.

I know nothing about Netezza, but I don't think this could (or should) be done using SQL in any convenient way. — Anders, Jul 15 '15 at 13:02
Use Nzlua function https://www-304.ibm.com/connections/forums/html/topic?id=33f35cc8-1d09-4444-93ab-493454a020ab — Abhis, Aug 06 '15 at 19:23

score 0 · Answer 1 · answered Jul 15 '15 at 18:37

0

Sometimes excel can be your best IDE. First grab the distinct list of mth_ids.

select distinct mth_id 
from table 
order by 1

Copy the list and paste in excel. Add a formula to build the case statement. enter image description here

Paste case statement from Excel to IDE and complete the query:

select sum(case when mth_id = 201306 then vol else null end) as "201306"
,sum(case when mth_id = 201307 then vol else null end) as "201307"
,sum(case when mth_id = 201308 then vol else null end) as "201308"
,sum(case when mth_id = 201309 then vol else null end) as "201309"
,sum(case when mth_id = 201310 then vol else null end) as "201310"
from table

answered Jul 15 '15 at 18:37

Niederee

4,155
25
38

Thanks for this solution. – Sandy_Unknown Jul 16 '15 at 07:04
Thanks for this solution. But mth_id column always has different number of rows with different values. – Sandy_Unknown Jul 16 '15 at 07:13
If you need to automate it rather then one time run it. You would use a scripting language. – Niederee Jul 16 '15 at 19:39

column to row conversion of data in netezza

1 Answers1