Page 1 of 1

Convert rows into column in select

PostPosted: Thu May 28, 2015 2:03 am
by k singh
hi

I need a little help.
I have table with structure as follows:
table1
Year   Id1 ID2  ID3
2001   1   20   Null
2001   2   400  Null
2002   1   100  Null
2002   2   50   Null
2002   3   null 60
2002   4   88   Null
2005   3   Null 45


Each year can have upto 4 rows with Id1 as (1,2,3,4). If ID2 has value then ID3 will be null and vice versa
I want my output as
Key1   Val1   Val2   Val3   Val4
2001   20      400   Null   Null
2002   100      50   60      88
2005   null   null   45      null


I have designed a query
SELECT Year,
 (CASE WHEN Id1 = 1 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val1,
 (CASE WHEN Id1 = 2 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val2,
 (CASE WHEN Id1 = 3 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val3,
(CASE WHEN Id1 =  4 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END)) AS Val4
FROM Sales
GROUP BY Year


WIll This work to get my desired output. If not Any suggestions/help will be appreciated

Re: Convert rows into column in select

PostPosted: Wed Jun 17, 2015 1:21 am
by alexm
Hi k singh,

Running your query results in
DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID

Looks like the problem sits in the GROUP BY specification.

I've created a possible solution by this query:
WITH DATA0 (YEAR)                                           
 AS (SELECT YEAR FROM SALES GROUP BY YEAR)                   
,    DATA1 (YEAR ,VAL1)                                     
 AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 1)
,    DATA2 (YEAR ,VAL2)                                     
 AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 2)
,    DATA3 (YEAR ,VAL3)                                     
 AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 3)
,    DATA4 (YEAR ,VAL4)                                     
 AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 4)
SELECT D0.YEAR ,D1.VAL1 ,D2.VAL2 ,D3.VAL3 ,D4.VAL4           
FROM   DATA0 D0                                             
LEFT JOIN DATA1 D1 ON D1.YEAR = D0.YEAR                     
LEFT JOIN DATA2 D2 ON D2.YEAR = D0.YEAR                     
LEFT JOIN DATA3 D3 ON D3.YEAR = D0.YEAR                     
LEFT JOIN DATA4 D4 ON D4.YEAR = D0.YEAR                     
;                                                           
---------+---------+---------+---------+---------+---------+-
YEAR    VAL1    VAL2    VAL3    VAL4                         
---------+---------+---------+---------+---------+---------+-
2001      20     400  ------  ------                         
2002     100      50      60      88                         
2005  ------  ------      45  ------                         
DSNE610I NUMBER OF ROWS DISPLAYED IS 4                       
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 


Depending on the amount of rows to process, this may result in an expensive operation (unfortunately you didn't provide table size/index defs).

As an alternative, you may use some of the available XML aggregate and serialize funtionality (see also this thread)

Best regards,
alexm

Re: Convert rows into column in select

PostPosted: Wed Jun 17, 2015 1:21 pm
by NicC
WIll This work to get my desired output

The best way to find out is not ask someone do do your work for you but to try it yourself and then if you have a problem that you or your colleagues cannot answer to ask on a forum. And this sort of query has been asked before on forums and the last time was only a week or two ago. There is a forum search function that should be used before posting.