1

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!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Quinn
  • 23
  • 1
  • 4

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • You can do it dynamically using dynamic SQL in a stored function. The linked question shows how to do it. – Barmar Aug 16 '17 at 23:14
  • @Barmar, I mean you can't do it in a single SQL statement. Doing multiple statements in a stored proc doesn't change that. – Bill Karwin Aug 16 '17 at 23:40
  • Yes, that's true. There's no shortcut in SQL, but there are ways to make a shorcut in MySQL because it has procedures. – Barmar Aug 16 '17 at 23:41
  • @Barmar, one can do exactly the same thing in any programming language. – Bill Karwin Aug 16 '17 at 23:43
  • This doesn't exactly answer my question but it does help me see what I need to do. So basically I'll need to form a query like the above using a procedure. Find all the tags that exist, generate the above query, and ezpz. Am I right here? – Quinn Aug 17 '17 at 00:17
  • @Quinn, yes, that's right. – Bill Karwin Aug 17 '17 at 00:24