今天做查询统计的时候遇到了一个问题，就是要查出类似数据报表的数据。数据库里面查数据不是我需要的，需要自己组装数据，觉得麻烦。
例如：
SELECT count(*)shuliang, j.zhuangtaibh, a.zhuanlilx FROM aj_ajxxb a left JOIN aj_ztls j ON a.wofangjh = j.wofangjh AND j.zhuangtaibh <> '' AND a.zhuanlilx IN(1, 2, 3) GROUP BY j.zhuangtaibh, a.zhuanlilx
而我想要的专利类型1,2,3都在一行，这时就需要将列转行？
我的方法是：
SELECT temp_xx.zhuangtaibh 状态编号, CASE WHEN temp_xx.zhuanlilx = '1' THEN shuliang ELSE '' END AS '发明', CASE WHEN temp_xx.zhuanlilx = '2' THEN shuliang ELSE '' END AS '新型', CASE WHEN temp_xx.zhuanlilx = '3' THEN shuliang ELSE '' END AS '外观' FROM ( SELECT count(*)shuliang, j.zhuangtaibh, a.zhuanlilx FROM aj_ajxxb a JOIN aj_ztls j ON a.wofangjh = j.wofangjh AND j.zhuangtaibh <> '' AND a.zhuanlilx IN(1, 2, 3) GROUP BY j.zhuangtaibh, a.zhuanlilx )AS temp_xx
成为这样的数据，还是不符合要求
想到group_concat()函数分组能够和合并行数据，正好满足我的要求：
SELECT t1.zhuangtaibh, group_concat(faming SEPARATOR ' ')AS 发明, group_concat(xinxing SEPARATOR ' ')AS 新型, group_concat(waiguan SEPARATOR ' ')AS 外观FROM( SELECT temp_xx.zhuangtaibh,CASE WHEN temp_xx.zhuanlilx = '1' THEN shuliang ELSE ''END AS 'faming',CASE WHEN temp_xx.zhuanlilx = '2' THEN shuliang ELSE ''END AS 'xinxing',CASE WHEN temp_xx.zhuanlilx = '3' THEN shuliang ELSE ''END AS 'waiguan' FROM( SELECT count(*)shuliang, j.zhuangtaibh, a.zhuanlilx FROM aj_ajxxb a JOIN aj_ztls j ON a.wofangjh = j.wofangjh AND j.zhuangtaibh <> '' AND a.zhuanlilx IN(1, 2, 3) GROUP BY j.zhuangtaibh, a.zhuanlilx )AS temp_xx )AS t1GROUP BY t1.zhuangtaibh
这样基本我的要求，可是怎么优化Sql？？？请大神帮忙想想。