mysql 主表转宽表_将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