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'
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'
UNION ALL
SELECT A.cmp_name,A.dep_name2 FROM cmp A
UNION ALL
SELECT A.cmp_name,A.dep_name3 FROM cmp AUNION ALL
SELECT A.cmp_name,A.dep_name4 FROM cmp AUNION ALL
SELECT A.cmp_name,A.dep_name5 FROM cmp A
ORDER BY cmp_name
MySQL交叉表
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;
+----------+-----------+-----------+-----------+-----------+-----------+
| 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 preparedmysql> 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>
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 ;