Quinn Quinn - 1 year ago 68
MySQL Question

MYSQL - True/False Dynamic Pivot Table

I'm trying to form a table with unique column values as the column names and I'm not sure how to do that. The values will be either true or false/null.

Here's the setup:

PROJECTS

ProjectID | ....
---------------------------
1111 | .....
2222 | .....


PROJECT_TAGS

TagID | ProjectID | TagDefID (TagNameID fkey)
-----------------------------
1 | 1111 | 5
2 | 1111 | 6
3 | 2222 | 5


TAG_NAMES

TagNameID | TagName
-----------------------------
5 | Is Really Cool
6 | Is Awesome


RESULT

ProjectID | Is Really Cool | Is Awesome
-----------------------------------------
1111 | true | true
2222 | true |


Now the tricky part is that additional tag types will be added as time goes on. It seems really gross and slow to have to iterate over each project ID so I was hoping there would be some sort of magic SQL trick to sort this out. Basically I need to make an XML spreadsheet type view of the database because reasons.

Thanks!

Answer Source

You can do a pivot table this way:

SELECT P.ProjectID,
  MAX(CASE WHEN PT.TagDefID=5 THEN true END) AS `Is Really Cool`,
  MAX(CASE WHEN PT.TagDefID=6 THEN true END) AS `Is Awesome`
FROM PROJECTS AS P
LEFT OUTER JOIN PROJECT_TAGS AS PT ON P.ProjectID = PT.ProjectID
GROUP BY P.ProjectID;

SQL has no special magic to pivot for tags it discovers in the data. The reason for this is that the SQL select-list must explicitly name all the columns that will be in the result, and the query must name them before it starts reading the data. A query can't dynamically add columns to the result set on the fly.

So you need to do a pivot table in two steps:

  1. Query the possible tags.
  2. Format the SQL query with one column per tag.

There is no short-cut for this in SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download