Pivot table using MySQL
I have two tables Triples and Tags
The triplet table has the following columns
id PostID TagID Value
1 1 1 Murder
2 1 2 New Brunswick
3 2 1 Theft
4 2 3 Gun
The tag table contains the following columns
id TagName
1 Incident
2 Location
3 Weapon
I am trying to write sql to create a pivot table with dynamic headers
The output should look like this
PostID Incident Location Weapon
1 Murder New Brunswick
2 Theft Gun
Any help writing SQL would be appreciated. I've seen examples on the internet but couldn't figure it out.
+2
source to share
1 answer
To expand data in MySQL, you will need to use both an aggregate function and an expression CASE
.
If you have a known number of columns, you can program the query:
select p.postid,
max(case when t.tagname = 'Incident' then p.value end) Incident,
max(case when t.tagname = 'Location' then p.value end) Location,
max(case when t.tagname = 'Weapon' then p.value end) Weapon
from triples p
left join tags t
on p.tagid = t.id
group by p.postid;
But if you have an unknown number of columns, you will need to use a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN TagName = ''',
TagName,
''' THEN p.value END) AS `',
TagName, '`'
)
) INTO @sql
FROM tags;
SET @sql
= CONCAT('SELECT p.postid, ', @sql, '
from triples p
left join tags t
on p.tagid = t.id
group by p.postid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo .
Both will give the result:
| POSTID | INCIDENT | LOCATION | WEAPON |
----------------------------------------------
| 1 | Murder | New Brunswick | (null) |
| 2 | Theft | (null) | Gun |
+11
source to share