mysql数据库，行变成列的sql，如何写？

[oraclemch]

create table cmp(cmp_name varchar(32), dep_name1 varchar(16), dep_name2 varchar(16), dep_name3 varchar(16), dep_name4 varchar(16), dep_name5 varchar(16));
insert into cmp select 'CPIC', 'IT','SC','RA','DA','DT';
insert into cmp select 'SZHW', 'IT',' ','RA',' ','DT';
insert into cmp select 'ZGRS', 'SA','ST',' ','DA','DT';
select * from cmp;

-- 查询结果如下
'CPIC', 'IT', 'SC', 'RA', 'DA', 'DT'
'SZHW', 'IT', ' ', 'RA', ' ', 'DT'
'ZGRS', 'SA', 'ST', ' ', 'DA', 'DT'


-- 现在我想得到如下结果，行变成列，sql如何写？
'CPIC', 'IT'
'CPIC', 'SC'
'CPIC', 'RA'
'CPIC', 'DA'
'CPIC', 'DT'
'SZHW', 'IT'
'SZHW', 'RA'
'SZHW', 'DT'
'ZGRS', 'SA'
'ZGRS', 'ST'
'ZGRS', 'DA'
'ZGRS', 'DT'

[wwwwb]

SELECT A.cmp_name,A.dep_name1 FROM cmp A
UNION ALL
SELECT A.cmp_name,A.dep_name2 FROM cmp A
UNION ALL
SELECT A.cmp_name,A.dep_name3 FROM cmp A

UNION ALL
SELECT A.cmp_name,A.dep_name4 FROM cmp A

UNION ALL
SELECT A.cmp_name,A.dep_name5 FROM cmp A
ORDER BY cmp_name

[yangbang_19856]

笔者可以不防再试一下，纵表转横表。

[oraclemch]

[Quote=引用 1 楼 wwwwb 的回复:]

SELECT A.cmp_name,A.dep_name1 FROM cmp A
UNION ALL
SELECT A.cmp_name,A.dep_name2 FROM cmp A
UNION ALL
SELECT A.cmp_name,A.dep_name3 FROM cmp A

UNION ALL
SELECT A.cmp_name,A.dep_name4 FROM cmp……
[/Quote]

谢谢，这个思路，我明白了，但是不太可取，因为我的表字段太多了！

[wwwwb]

用SP取得所有字段名，用字符串累加生成SQL语句，再执行

[ACMAIN_CHM]

参考下贴中多种方法吧。

http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
MySQL交叉表

[ACMAIN_CHM]

[Quote]4. 动态，适用于列不确定情况，

mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;



mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)

mysql>

[/Quote]

[mysqldbd]

[Quote=引用 6 楼 acmain_chm 的回复:]

引用4. 动态，适用于列不确定情况，

mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;



mysql> SET @QQ=CONCAT('SELECT ifnull(c……
[/Quote]

谢谢，你的这是 行变列，我要的是 列变行 的例子。

[mysqldbd]

不要意思，发错地方了@

[ACMAIN_CHM]

SET @EE=''; 
select @EE:=CONCAT(@EE,'select cmp_name,',COLUMN_NAME,' from cmp where ',COLUMN_NAME,'!=\'  \' union all ') from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='csdn' and TABLE_NAME='cmp' and COLUMN_NAME!='cmp_name';
set @EE:=left(@EE,length(@EE)-length('union all '));
PREPARE stmt2 FROM @EE;
EXECUTE stmt2;

[ACMAIN_CHM]

mysql> select * from cmp;
+----------+-----------+-----------+-----------+-----------+-----------+
| cmp_name | dep_name1 | dep_name2 | dep_name3 | dep_name4 | dep_name5 |
+----------+-----------+-----------+-----------+-----------+-----------+
| CPIC     | IT        | SC        | RA        | DA        | DT        |
| SZHW     | IT        |           | RA        |           | DT        |
| ZGRS     | SA        | ST        |           | DA        | DT        |
+----------+-----------+-----------+-----------+-----------+-----------+
3 rows in set (0.03 sec)

mysql>
mysql> SET @EE='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @EE:=CONCAT(@EE,'select cmp_name,',COLUMN_NAME,' from cmp where ',
COLUMN_NAME,'!=\'  \' union all ') from INFORMATION_SCHEMA.COLUMNS where TABLE_S
CHEMA='csdn' and TABLE_NAME='cmp' and COLUMN_NAME!='cmp_name';
+-------------------------------------------------------------------------------
| @EE:=CONCAT(@EE,'select cmp_name,',COLUMN_NAME,' from cmp where ',COLUMN_NAME,
'!=\'  \' union all ')
+-------------------------------------------------------------------------------
| select cmp_name,dep_name1 from cmp where dep_name1!='  ' union all

....

5 rows in set (0.02 sec)

mysql> set @EE:=left(@EE,length(@EE)-length('union all '));
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @EE;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt2;
+----------+-----------+
| cmp_name | dep_name1 |
+----------+-----------+
| CPIC     | IT        |
| SZHW     | IT        |
| ZGRS     | SA        |
| CPIC     | SC        |
| ZGRS     | ST        |
| CPIC     | RA        |
| SZHW     | RA        |
| CPIC     | DA        |
| ZGRS     | DA        |
| CPIC     | DT        |
| SZHW     | DT        |
| ZGRS     | DT        |
+----------+-----------+
12 rows in set (0.00 sec)

mysql>

[zuoxingyu]

狼头威武，WWWA威武。学习了。

[lxq19851204]

[Quote=引用 10 楼 acmain_chm 的回复:]
SQL code
mysql> select * from cmp;
+----------+-----------+-----------+-----------+-----------+-----------+
| cmp_name | dep_name1 | dep_name2 | dep_name3 | dep_name4 | dep_name5 |
+----------+---……
[/Quote]
太强了,斑竹

[wwwwb]

or
DELIMITER $$

USE `ee`$$

DROP PROCEDURE IF EXISTS `CTOR`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ctor`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE dd2,dd3 VARCHAR(5000);
DECLARE dd1 VARCHAR(5000);
DECLARE DD CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='ee' AND TABLE_NAME='cmp' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET dd3='';
OPEN dd;
FETCH dd INTO dd2;
WHILE done=0 DO
SET dd1='select cmp_name,';
IF LOWER(dd2)<>'cmp_name' THEN
SET dd3=CONCAT(dd3,dd1,dd2,' from cmp union all ');
END IF;
FETCH dd INTO dd2;
END WHILE;
SELECT LEFT(dd3,LENGTH(dd3)-10);
SET @dd4=LEFT(dd3,LENGTH(dd3)-10);
PREPARE stml FROM @dd4;
EXECUTE stml;
END$$

DELIMITER ;