有如下一行数据
学生1 学生2 学生3 语文1 语文2 语文3 数学1 数学2 数学3
贾某 艺谋 并某 69 78 90 56 85 89
请问如何转换成：
学生 语文 数学
贾某 69 56
艺谋 78 85
并某 90 89
总分 237 230 467
------解决方案--------------------
- SQL code
select 学生1,语文1,数学1 from tbunion allselect 学生2,语文2,数学2 from tbunion allselect 学生3,语文3,数学3 from tbunion allselect '总分',语文1+语文2+语文3,数学1+数学2+数学3 from tb
------解决方案--------------------
- SQL code
--构建示例数据 create table #ta(学生1 varchar(10), 学生2 varchar(10), 学生3 varchar(10), 语文1 int, 语文2 int, 语文3 int, 数学1 int, 数学2 int, 数学3 int)insert into #ta select '贾某', '艺谋', '并某', 69, 78, 90, 56, 85, 89--解决方案演示;with CET1 as(select 姓名 as 学生, sum(语文)语文,sum(数学) 数学 from(select *,left(科目,2) as 科目2 from #ta unpivot (分数 for 科目 in([语文1],[语文2],[语文3],[数学1],[数学2],[数学3]))u unpivot (姓名 for 学生 in([学生1],[学生2],[学生3]) )u1where right(科目,1) = right(学生,1))apivot(sum(分数) for 科目2 in([语文],[数学]) )pgroup by 姓名)select *,'' from CET1unionselect '总分',sum(语文),sum(数学),CAST(sum(语文+数学) AS VARCHAR(10))from cet1/*学生 语文 数学 ---------- ----------- ----------- ----------并某 90 89 贾某 69 56 艺谋 78 85 总分 237 230 467(4 行受影响)*/
------解决方案--------------------
假设你有很多学生，以下是动态语句
- SQL code
--构建示例数据 create TAble #TA(学生1 varchar(10), 学生2 varchar(10), 学生3 varchar(10), 语文1 int, 语文2 int, 语文3 int, 数学1 int, 数学2 int, 数学3 int)insert into #TA select '贾某', '艺谋', '并某', 69, 78, 90, 56, 85, 89DECLARE @SQL VARCHAR(MAX),@sql1 varchar(max),@sql2 varchar(max),@sql3 varchar(max),@sql4 varchar(max),@sql5 varchar(max)select @sql1 = isnull(@sql1,'') +'['+ left(name,2) +'],' from (select distinct left(name,2) as name from tempdb.sys.syscolumns where id = object_id('tempdb..#TA') and name not like '学生%')aset @sql1 = left(@sql1,len(@sql1)-1)select @sql2 = isnull(@sql2,'') +'['+ left(name,2) +']+' from (select distinct left(name,2) as name from tempdb.sys.syscolumns where id = object_id('tempdb..#TA') and name not like '学生%')aset @sql2 = left(@sql2,len(@sql2)-1)select @sql3 = isnull(@sql3,'') +'sum(['+ left(name,2) +'])'+ left(name,2) +',' from (select distinct left(name,2) as name from tempdb.sys.syscolumns where id = object_id('tempdb..#TA') and name not like '学生%')aset @sql3 = left(@sql3,len(@sql3)-1)select @sql4 = isnull(@sql4,'') +'['+ name +'],' from tempdb.sys.syscolumns where id = object_id('tempdb..#TA') and name like '学生%'set @sql4 = left(@sql4,len(@sql4)-1)select @sql5 = isnull(@sql5,'') +'['+ name +'],' from tempdb.sys.syscolumns where id = object_id('tempdb..#TA') and name not like '学生%'set @sql5 = left(@sql5,len(@sql5)-1)--解决方案演示set @SQL = ';with CET1 as(select 姓名 as 学生, [email protected]+' from(select *,left(科目,2) as 科目2 from #TA unpivot (分数 for 科目 in([email protected]+'))u unpivot (姓名 for 学生 in([email protected]+') )u1where right(科目,1) = right(学生,1))apivot(sum(分数) for 科目2 in([email protected]+') )pgroup by 姓名)select *,'''' from CET1unionselect ''总分'',sum(语文),sum(数学),CAST(sum([email protected]+') AS VARCHAR(10))from cet1 'PRINT @SQLEXEC(@SQL)/*学生 语文 数学 ---------- ----------- ----------- ----------并某 90 89 贾某 69 56 艺谋 78 85 总分 237 230 467(4 行受影响)*/