MSSQL中的行转列

 

--创建测试数据表;
CREATE TABLE test
(
	a VARCHAR(10),
	b INT,
	c INT,
	d INT,
	e INT
);

--添加测试数据
INSERT INTO test VALUES('x',1,2,3,4);
INSERT INTO test VALUES('y',5,6,7,8);
INSERT INTO test VALUES('z',9,10,11,12);

--拼接SQL语句,生成一个最终表,用于存放最后的结果集

DECLARE @strSQL VARCHAR(MAX);
SET @strSQL = 'CREATE TABLE test1(名称 VARCHAR(10) ';
SELECT @strSQL = @strSQL + ',' + a + ' VARCHAR(10) ' FROM test;
SET @strSQL = @strSQL + ');';

--执行SQL;
EXEC(@strSQL);

--新建一个游标,循环读取测试数据的表的字段名称;
DECLARE @name VARCHAR(10);
DECLARE _cursor CURSOR FOR SELECT name FROM SYSCOLUMNS WHERE ID = OBJECT_ID(N'test','U');
OPEN _cursor;
FETCH NEXT FROM _cursor INTO @name;
WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('SELECT ' + @name + ' AS t INTO test2 FROM test'); --将测试数据表的指定列的数据,写入中间表test2;
		SET @strSQL = N'INSERT INTO test1 SELECT ''' + @name + ''''; 
		SELECT @strSQL += ',''' + CAST(t AS VARCHAR(10)) + '''' FROM test2;  --将中间表的列数据转换成行数据,插入最终表中;
		EXEC(@strSQL); --执行SQL
		EXEC('DROP TABLE test2'); --删除中间表,用于下次生成;不过这里应该可以用到虚拟表或者TRUNCATE TABLE;
		FETCH NEXT FROM _cursor INTO @name;
	END

CLOSE _cursor;
DEALLOCATE _cursor;

--对比原始数据和最终数据的结果展示
SELECT * FROM test;
SELECT * FROM test1;

--删除表;
DROP TABLE test;
DROP TABLE test1;