将MySQL的窄表转换成宽表的方法

将MySQL的窄表转换成宽表的方法

在扩展设计中，使用窄表可以很方便的增加新的项。如果用宽表，就会需要修改表结构，很不方便。

而使用宽表在查询过滤数据的时候会比窄表方便很多，数据的记录量也会少很多。

这里介绍一种将窄表转置成宽表的语法：

SELECT 
    userkey,
    MAX(CASE tagid
        WHEN 1 THEN valueID
        ELSE NULL
    END) AS tag1,
    MAX(CASE tagid
        WHEN 2 THEN valueID
        ELSE NULL
    END) AS tag2,
    MAX(CASE tagid
        WHEN 3 THEN valueID
        ELSE NULL
    END) AS tag3,
    MAX(CASE tagid
        WHEN 4 THEN valueID
        ELSE NULL
    END) AS tag4,
    MAX(CASE tagid
        WHEN 5 THEN valueID
        ELSE NULL
    END) AS tag5,
    MAX(CASE tagid
        WHEN 6 THEN valueID
        ELSE NULL
    END) AS tag6,
    MAX(CASE tagid
        WHEN 7 THEN tagvalue
        ELSE NULL
    END) AS tag7,
    MAX(CASE tagid
        WHEN 8 THEN tagvalue
        ELSE NULL
    END) AS tag8,
    MAX(CASE tagid
        WHEN 9 THEN tagvalue
        ELSE NULL
    END) AS tag9,
    MAX(CASE tagid
        WHEN 10 THEN tagvalue
        ELSE NULL
    END) AS tag10,
    MAX(CASE tagid
        WHEN 11 THEN tagvalue
        ELSE NULL
    END) AS tag11,
    MAX(CASE tagid
        WHEN 12 THEN tagvalue
        ELSE NULL
    END) AS tag12,
    MAX(CASE tagid
        WHEN 13 THEN tagvalue
        ELSE NULL
    END) AS tag13,
    MAX(CASE tagid
        WHEN 14 THEN tagvalue
        ELSE NULL
    END) AS tag14,
    MAX(CASE tagid
        WHEN 15 THEN tagvalue
        ELSE NULL
    END) AS tag15,
    MAX(CASE tagid
        WHEN 16 THEN tagvalue
        ELSE NULL
    END) AS tag16,
    MAX(CASE tagid
        WHEN 17 THEN tagvalue
        ELSE NULL
    END) AS tag17,
    MAX(CASE tagid
        WHEN 18 THEN tagvalue
        ELSE NULL
    END) AS tag18,
    MAX(CASE tagid
        WHEN 19 THEN tagvalue
        ELSE NULL
    END) AS tag19,
    MAX(CASE tagid
        WHEN 20 THEN tagvalue
        ELSE NULL
    END) AS tag20,
    MAX(CASE tagid
        WHEN 21 THEN tagvalue
        ELSE NULL
    END) AS tag21,
    MAX(CASE tagid
        WHEN 22 THEN tagvalue
        ELSE NULL
    END) AS tag22,
    MAX(CASE tagid
        WHEN 23 THEN tagvalue
        ELSE NULL
    END) AS tag23,
    MAX(CASE tagid
        WHEN 24 THEN valueID
        ELSE NULL
    END) AS tag24,
    MAX(CASE tagid
        WHEN 25 THEN valueID
        ELSE NULL
    END) AS tag25,
    MAX(CASE tagid
        WHEN 26 THEN valueID
        ELSE NULL
    END) AS tag26,
    MAX(CASE tagid
        WHEN 27 THEN valueID
        ELSE NULL
    END) AS tag27,
    MAX(CASE tagid
        WHEN 28 THEN valueID
        ELSE NULL
    END) AS tag28,
    MAX(CASE tagid
        WHEN 29 THEN valueID
        ELSE NULL
    END) AS tag29,
    MAX(CASE tagid
        WHEN 30 THEN valueID
        ELSE NULL
    END) AS tag30,
    MAX(CASE tagid
        WHEN 31 THEN valueID
        ELSE NULL
    END) AS tag31,
    MAX(CASE tagid
        WHEN 32 THEN valueID
        ELSE NULL
    END) AS tag32
FROM
    userdata
WHERE
    tagid IN (1 , 2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11,
        12,
        13,
        14,
        15,
        16,
        17,
        18,
        19,
        20,
        21,
        22,
        23,
        24,
        25,
        26,
        27,
        28,
        29,
        30,
        31,
        32)
GROUP BY userkey