3

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
Community
  • 1
  • 1
Dexygen
  • 12,287
  • 13
  • 80
  • 147

1 Answers1

8

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.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Works like a charm though I don't understand precisely how -- any insight into the implementation would be appreciated – Dexygen Jan 03 '13 at 17:04
  • @GeorgeJempty see my edit, I included a brief explanation of how this works. Let me know if you need more details. – Taryn Jan 03 '13 at 17:11
  • Got it; I know about aggregate functions of course but "max" just didn't seem germane to me. Nice bit of trickeration ;) – Dexygen Jan 04 '13 at 14:18
  • @GeorgeJempty since the value is a string you are stuck using `max` or `min`. :) – Taryn Jan 04 '13 at 14:19