complex dynamic SQL query, joining on self

Question

I'm looking to tackle a difficult SQL query. I'd appreciate some input on how to achieve this relatively complex SQL query on a table.

I currently have a table as follows:

caseId scanId attribute
-----------------------
  1      2       A
  1      4       A
  2      3       B
  3     NULL    NULL

I want to be able to achieve this table:

caseId scanId1 scanId2 attribute1 attribute2 count
--------------------------------------------------
  1       2       4        A          A        2
  2       3      NULL      B        NULL       1
  3     NUL      NULL     NULL      NULL       0

I know this involves joining the table on itself, however I can't come up with how to dynamically make the number of columns (scanId1, scanId2, etc.) required, which depends on the number of unique caseId tuples.

Any tips on how I can get this to work?

Thanks in advance!

Google: "MySQL dynamic pivot". If you have a dynamic number of columns, then you need to use a `prepare`/`execute` statement and there are numerous good answers on this subject. — Gordon Linoff, Nov 20 '14 at 16:39
It's a pity though that you have no presentation layer available (like PHP for instance) — Strawberry, Nov 20 '14 at 16:42
@Gordon - [Googling MSSQL dynamic pivot](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot) gets you back to stack overflow [here...](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot) — 576i, Nov 20 '14 at 16:53
thanks, I am using these queries with PHP so I was able to handle the data that way rather than try and create a pivot table with mySQL itself — BearAttack, Nov 21 '14 at 15:20

score 0 · Answer 1 · edited Apr 28 '15 at 10:14

You can try this. It works

SELECT
    caseId,
    MAX(CASE WHEN scanId IN (2,3) THEN scanId ELSE NULL END) AS scanId1,
    MAX(CASE WHEN scanId=4 THEN scanId ELSE NULL END ) AS scanId2,
    MAX(CASE WHEN attribute =('A', 'B')THEN attribute ELSE NULL END) AS attribute1,
    MAX(CASE WHEN attribute IN ('A') THEN attribute ELSE NULL END ) AS attribute2,    
    count(scanId) AS counts    
FROM 
    GGG    
GROUP BY
    caseId

complex dynamic SQL query, joining on self

1 Answers1