[求SQL]带字符串的行列转换

[kimsung]

数据

create table tx(
c1 char(2),
c2 char(2),
c3 char(100),
);

insert into tx values
('A1','B1','hello'),
('A2','B1','nihao'),
('A1','B2','goodbye'),
('A2','B2','zaijian'),
('A1','B3','eat'),
('A2','B3','chi'),
('A1','B4','mouse'),
('A2','B4','laoshu'),
('A3','B4','shubiao'),
('A1','B4','monitor'),
('A2','B4','xianshiqi'),
('A3','B4','jianshi'),

期望结果

+------+------+--------+-----+----------+
|C1    |B1    |B2      |B3   |B4        |
+------+------+--------+-----+----------+
|A1    |hello |goodbye |eat  |monitor   |
|A2    |nihao |zaijian |chi  |xianshiqi |
|A3    |      |        |mouse|jianshi   |
+------+------+--------+-----+----------+

[小小小小周]

用case when 可以的；

[kimsung]

试过吗?

[ACMAIN_CHM]

参考下贴中的多种方法。

http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx

[kimsung]

解决了.
看来用max就可以.

1楼说的case when 是查不到的. 他只能查询group里的第一条数据.得外加max.
但是速度很慢.. -_-;

[kimsung]

ACMAIN终于出现了啊.
我是从你文章看的. 但是sum是似乎不好使的.
因为他是字符串.

[kimsung]

把SQL写下来. 测试过可以. 但还是不确定是不是有其他问题.

SELECT c1,
       MAX(CASE c2 WHEN 'B1' THEN c3) AS B1,
       MAX(CASE c2 WHEN 'B2' THEN c3) AS B2,
       MAX(CASE c2 WHEN 'B3' THEN c3) AS B3,
       MAX(CASE c2 WHEN 'B4' THEN c3) AS B4
  FROM tx
 GROUP BY c1

[ACMAIN_CHM]

[Quote]我是从你文章看的. 但是sum是似乎不好使的.[/Quote]你可以用MAX，MIN等。

[小小小小周]

刚没时间回,
select * from tx;


SELECT c1,
MAX(CASE c2 WHEN 'B1' THEN c3 end) AS `B1`,
MAX(CASE c2 WHEN 'B2' THEN c3 end) AS `B2`,
MAX(CASE c2 WHEN 'B3' THEN c3 end) AS `B3`,
MAX(CASE c2 WHEN 'B4' THEN c3 end) AS `B4`
FROM tx
GROUP BY c1;

select c1,max(case when c2='B1' then c3 else '' end)  as `B1`,
max(case when c2='B2' then c3 else '' end)  as `B2`,
max(case when c2='B3' then c3 else '' end)  as `B3`,
max(case when c2='B4' then c3 else '' end)  as `B4` 
from tx
GROUP BY C1

这样就可以了

[kimsung]

谢谢楼上.
解决了. ^^;

[zncggaofei]

学习，一直不了解as , group by 的涵义