sql server中实现行转列的代码

--创建测试数据表;
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;