mysql 数据透视表,使用MySQL的数据透视表

I have two tables Triples and Tags

Triples 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

Tags Table has 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

Output should be like this

PostID Incident Location Weapon

1 Murder New Brunswick

2 Theft Gun

Any help in writing the SQL would be appreciated. I have seen examples online but could not figure out this one

解决方案

In order to pivot the data in MySQL, you will need to use both an aggregate function and a CASE expression.

If you have a known number of columns, then you can hard-code 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, then 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;

Both will give the result:

| POSTID | INCIDENT | LOCATION | WEAPON |

----------------------------------------------

| 1 | Murder | New Brunswick | (null) |

| 2 | Theft | (null) | Gun |