mysql 多行合并成一行多列--根据一个帖子改的

[lionking123456]

id code name value
----------- ---------- --------
1 2014000 A 10
2 2014000 B 9
3 2014000 C 11
4 2014000 D 12
5 2014001 E 100
6 2014001 F 110
7 2014001 G 120

期望结果：
code name1 value1 name2 value2 name3 value3 name4 value4

----------- --------- --------- --------- --------- ----------- --------- --------- ---------
2014000 A 10 B 9 C 11 D 12
2014001 E 100 F 110 G 120 null nulll

CREATE TABLE T (ID INT, code INT, NAME CHAR(1), VALUE VARCHAR(10));
INSERT INTO T VALUES(1,2014000,'A','10'), (2,2014000,'B','9'), (3,2014000,'C','11'), (4,2014000,'D','12'), (5,2014001,'E','100'), (6,2014001,'F','110'), (7,2014003,'G','120');
-----下面的min max只能转一个code两行，多行如何处理？比如4或更多?
SELECT T1.code, T2.name AS name1, T2.value AS value1, T3.name AS name2, T3.value AS value2 FROM( SELECT code,MIN(ID) AS ID1,CASE COUNT(code) WHEN 1 THEN NULL ELSE MAX(ID) END AS ID2 FROM T GROUP BY code ) T1 LEFT JOIN T T2 ON T1.ID1 = T2.ID LEFT JOIN T T3 ON T1.ID2 = T3.ID

参考http://bbs.csdn.net/topics/391919161

[zjcxc]

通常是这样

select code,
	max(case row_id when 1 then NAME end) as NAME1,
	max(case row_id when 1 then VALUE end) as VALUE1,
	max(case row_id when 2 then NAME end) as NAME2,
	max(case row_id when 2 then VALUE end) as VALUE2,
	max(case row_id when 3 then NAME end) as NAME3,
	max(case row_id when 3 then VALUE end) as VALUE3,
	max(case row_id when 4 then NAME end) as NAME4,
	max(case row_id when 4 then VALUE end) as VALUE4,
	max(case row_id when 6 then NAME end) as NAME5,
	max(case row_id when 6 then VALUE end) as VALUE5
from(
select *,(select count(*) from t t1 where t1.code=t.code and t1.id <=t.id) as row_id from t order by code, id
) data
group by code