求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

7 个解决方案

#1

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')


生成静态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]

GO
动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78

(2 行受影响)
*/

------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)

--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/

#2

select 学生1,语文1,数学1 from tb
union all
select 学生2,语文2,数学2 from tb
union all
select 学生3,语文3,数学3 from tb
union all
select '总分',语文1+语文2+语文3,数学1+数学2+数学3 from tb

#3

引用 1 楼  的回复:

SQL code
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Ro……

请不要复制粘贴，请针对性的回答问题，谢谢

#4

你看懂了还怕写不出来？不要老是直接要代码，提高不了的

#5

如果评选最积极论坛分子，我选DBA同学

#6

--构建示例数据 
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]) )u1
where right(科目,1) = right(学生,1))a
pivot(sum(分数)  for 科目2 in([语文],[数学]) )p
group by 姓名
)select *,''  from CET1
union
select '总分',sum(语文),sum(数学),CAST(sum(语文+数学) AS VARCHAR(10))from cet1
/*
学生         语文          数学          
---------- ----------- ----------- ----------
并某         90          89          
贾某         69          56          
艺谋         78          85          
总分         237         230         467

(4 行受影响)
*/

#7

假设你有很多学生，以下是动态语句

--构建示例数据 
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

DECLARE @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 '学生%')a
set @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 '学生%')a
set @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 '学生%')a
set @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+') )u1
where right(科目,1) = right(学生,1))a
pivot(sum(分数)  for 科目2 in('+@sql1+') )p
group by 姓名
)select *,''''  from CET1
union
select ''总分'',sum(语文),sum(数学),CAST(sum('+@sql2+') AS VARCHAR(10))from cet1 '
PRINT @SQL
EXEC(@SQL)
/*
学生         语文          数学          
---------- ----------- ----------- ----------
并某         90          89          
贾某         69          56          
艺谋         78          85          
总分         237         230         467

(4 行受影响)
*/