求sql语句，将一行转为多列有如下一行数据学生1 学生2 学生3 语文1 语文2 语文3 数学1 数学2 数学3贾某艺谋
求sql语句，将一行转为多列
有如下一行数据
学生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 学生, '+@sql3+' from(select *,left(科目,2) as 科目2 from #TA unpivot (分数 for 科目 in('+@sql5+'))u unpivot (姓名 for 学生 in('+@sql4+') )u1where right(科目,1) = right(学生,1))apivot(sum(分数) for 科目2 in('+@sql1+') )pgroup by 姓名)select *,'''' from CET1unionselect ''总分'',sum(语文),sum(数学),CAST(sum('+@sql2+') AS VARCHAR(10))from cet1 'PRINT @SQLEXEC(@SQL)/*学生 语文 数学 ---------- ----------- ----------- ----------并某 90 89 贾某 69 56 艺谋 78 85 总分 237 230 467(4 行受影响)*/