I've seen this SO question (How can I Pivot a table in DB2?) and some of the answers/comments mention being able to acheive the same effect with a case statement but I'm struggling mightily to acheive this, probably due to a few years away from much SQL.
Here is a snapshot of some data I have:
ID Date ErrID ErrDesc
---------------------------------------
164 2012-09-21 1402 Large V
164 2012-09-21 1409 Missing
416 2012-09-21 1409 Missing
1380 2012-09-21 1411 n - Mis
1500 2012-09-17 1411 n - Mis
1500 2012-09-21 1402 Large V
ID and Date taken together need to be unique in a query that would return something such as the following, where the empty slots are null. How can I achieve this, with a case statement or otherwise? Bear in mind I don't think our version of DB2 supports "decode", and the SQL I need also needs to be able to run on Derby for testing. Thanks in advance.
ID Date Err1402 Err1409 Err1411
-----------------------------------------------------------------------
164 2012-09-21 Large V Missing
416 2012-09-21 Missing
1380 2012-09-21 n - Mis
1500 2012-09-17 n - Mis
1500 2012-09-21 Large V
Here is a version of the query using CASE
with an aggregate function:
select id,
date,
max(case when errid = 1402 then ErrDesc else '' end) Err1402,
max(case when errid = 1409 then ErrDesc else '' end) Err1409,
max(case when errid = 1411 then ErrDesc else '' end) Err1411
from yourtable
group by id, date
order by id
See SQL Fiddle with Demo (sql server version). You can replace the empty string with a null
.
select id,
date,
max(case when errid = 1402 then ErrDesc else null end) Err1402,
max(case when errid = 1409 then ErrDesc else null end) Err1409,
max(case when errid = 1411 then ErrDesc else null end) Err1411
from yourtable
group by id, date
order by id
Edit #1: The key to getting this to work properly is the aggregate function. The CASE
statement is putting each of the ErrId
values into the columns but if you do not use the aggregate function you will wind up with multiple rows for each id, date. So if you use:
select id,
date,
(case when errid = 1402 then ErrDesc else '' end) Err1402,
(case when errid = 1409 then ErrDesc else '' end) Err1409,
(case when errid = 1411 then ErrDesc else '' end) Err1411
from yourtable
order by id
The result is:
| ID | DATE | ERR1402 | ERR1409 | ERR1411 |
-------------------------------------------------------------------------
| 164 | September, 21 2012 00:00:00+0000 | Large V | | |
| 164 | September, 21 2012 00:00:00+0000 | | Missing | |
| 416 | September, 21 2012 00:00:00+0000 | | Missing | |
| 1380 | September, 21 2012 00:00:00+0000 | | | n - Mis |
| 1500 | September, 17 2012 00:00:00+0000 | | | n - Mis |
| 1500 | September, 21 2012 00:00:00+0000 | Large V | | |
As you can see the id=1500
you will get multiple rows which you do not want. So if you tell it that you want the max()
value for each errid
, then you will get one row per record.