Example : Row to Column in MySQL

SELECT tmp_tbl.asset_id,
    MAX(CASE WHEN tmp_tbl.custom_sequence=1 THEN tmp_tbl.software_name ELSE NULL END) other_software_1,
    MAX(CASE WHEN tmp_tbl.custom_sequence=2 THEN tmp_tbl.software_name ELSE NULL END) other_software_2,
    MAX(CASE WHEN tmp_tbl.custom_sequence=3 THEN tmp_tbl.software_name ELSE NULL END) other_software_3,
    MAX(CASE WHEN tmp_tbl.custom_sequence=4 THEN tmp_tbl.software_name ELSE NULL END) other_software_4,
    MAX(CASE WHEN tmp_tbl.custom_sequence=5 THEN tmp_tbl.software_name ELSE NULL END) other_software_5,
    MAX(CASE WHEN tmp_tbl.custom_sequence=6 THEN tmp_tbl.software_name ELSE NULL END) other_software_6,
    MAX(CASE WHEN tmp_tbl.custom_sequence=7 THEN tmp_tbl.software_name ELSE NULL END) other_software_7,
    MAX(CASE WHEN tmp_tbl.custom_sequence=8 THEN tmp_tbl.software_name ELSE NULL END) other_software_8
FROM (
    SELECT d.asset_id, s.name AS software_name,
        row_number() OVER(PARTITION BY d.asset_id ORDER BY d.asset_id) AS custom_sequence
    FROM asset_detail d
    LEFT JOIN software s ON d.software_id = s.id
) AS tmp_tbl
GROUP BY tmp_tbl.asset_id

Result:

asset_id	other_software_1	other_software_2	other_software_3	other_software_4	other_software_5	other_software_6	other_software_7	other_software_8
1	SAP GUI	CHROME	FIREFOX	ANYDESK	NULL	NULL	NULL	NULL
2	SAP GUI	MS ACCESS 2010	FIREFOX	CHROME	ANYDESK	NULL	NULL	NULL
3	SAP GUI	CHROME	FIREFOX	ANYDESK	MICROSOFT TEAMS	NULL	NULL	NULL

Reff :

SELECT tmp_tbl.asset_id, MAX(CASE WHEN tmp_tbl.rn=1 THEN tmp_tbl.software_name ELSE NULL END) other_software_1, MAX(CASE WHEN tmp_tbl.rn=2 THEN tmp_tbl.software_name ELSE NULL END) other_software_2, MAX(CASE WHEN tmp_tbl.rn=3 THEN tmp_tbl.software_name ELSE NULL END) other_software_3, MAX(CASE WHEN tmp_tbl.rn=4 THEN tmp_tbl.software_name ELSE NULL END) other_software_4, MAX(CASE WHEN tmp_tbl.rn=5 THEN tmp_tbl.software_name ELSE NULL END) other_software_5, MAX(CASE WHEN tmp_tbl.rn=6 THEN tmp_tbl.software_name ELSE NULL END) other_software_6, MAX(CASE WHEN tmp_tbl.rn=7 THEN tmp_tbl.software_name ELSE NULL END) other_software_7, MAX(CASE WHEN tmp_tbl.rn=8 THEN tmp_tbl.software_name ELSE NULL END) other_software_8 FROM ( SELECT d.asset_id, s.name AS software_name, IF(@prev <> d.asset_id, @rn:=0, @rn), @prev:=d.asset_id, @rn:=@rn+1 AS rn FROM asset_detail d LEFT JOIN software s ON d.software_id = s.id , (SELECT @rn:=0) rn, (SELECT @prev:=0) prev ) AS tmp_tbl GROUP BY tmp_tbl.asset_id

arisnew/row-to-column-mysql.md

Select an option

No results found

Select an option

No results found

arisnew commented Jun 19, 2024

Uh oh!