SQL SERVER 行转列/列转行

https://www.cnblogs.com/Rawls/p/11027413.html

 

/*
    第一步：创建临时表结构
*/
CREATE TABLE #Student  --创建临时表
(
    StuName nvarchar(20),    --学生名称
    StuSubject nvarchar(20),--考试科目
    StuScore int            --考试成绩
)
DROP TABLE #Student      --删除临时表
SELECT * FROM #Student   --查询所有数据




/*
    第二步：写入测试数据
*/
--张三
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','语文',80);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','数学',75);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','英语',65);
--李四
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','语文',36);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','数学',56);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','英语',38);
--王五
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','语文',69);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','数学',80);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','英语',78);
--赵六
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','语文',80);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','数学',80);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','英语',95);

--1、case when 方法
SELECT StuSubject
    ,SUM(CASE WHEN StuName='张三' THEN StuScore END) as '张三'
    ,SUM(CASE WHEN StuName='王五' THEN StuScore END) as '王五'
    ,SUM(CASE WHEN StuName='赵六' THEN StuScore END) as '赵六'
FROM #Student
GROUP BY StuSubject

--2使用PIVOT 关键字
SELECT *
FROM #Student
PIVOT(SUM(StuScore) FOR [StuName] IN("李四","王五","张三","赵六")) AS T 

--方法3：使用PIVOT、EXEC关键字，动态执行
Declare @StuName varchar(100);
Declare @sql nvarchar(4000)
--步骤1.假设列不固定，是动态产生的，需要先将所有列组合成一个长字符串，比如A，B，C ，    下面的写法只能在SQL server2017中运行，更多将多行字段合并成一个字段方法参考：https://www.cnblogs.com/Rawls/p/10758788.html
SELECT @StuName='"'+STRING_AGG(StuName,'","')+'"'    
FROM (
    SELECT StuName from #Student GROUP BY StuName
) AS TE
--Print @StuName
--步骤2.由于动态产生的列，脚本不能执行，所以用Exec来执行，把脚本写成一个字符串。
SET @sql='
    SELECT *
    FROM #Student
    PIVOT(SUM(StuScore) FOR [StuName] IN('+@StuName+')) AS T 
    '
--步骤3.执行脚本
Exec(@sql)