sql 列转行

一、列转行

 

1、建立表格

 

ifobject_id('tb')isnotnulldroptabletb

 

go

 

createtabletb(姓名varchar(10),课程varchar(10),分数int)

 

insertintotbvalues('张三','语文',74)

 

insertintotbvalues('张三','数学',83)

 

insertintotbvalues('张三','物理',93)

 

insertintotbvalues('李四','语文',74)

 

insertintotbvalues('李四','数学',84)

 

insertintotbvalues('李四','物理',94)

 

go

 

select*fromtb

 

go

 

姓名       课程       分数

---------- ---------- -----------

 

张三       语文        74

 

张三       数学        83

 

张三       物理        93

 

李四       语文        74

 

李四       数学        84

 

李四       物理        94

 

  

 

2、使用SQL Server 2000静态SQL

 

--c

 

select姓名,

 

 max(case课程when'语文'then分数else0end)语文,

 

 max(case课程when'数学'then分数else0end)数学,

 

 max(case课程when'物理'then分数else0end)物理

 

fromtb

groupby姓名

 

姓名       语文        数学        物理

 

---------- ----------- ----------- -----------

 

李四        74          84          94

 

张三        74          83          93

 

  

 

3、使用SQL Server 2000动态SQL

 

--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

 

--变量按sql语言顺序赋值

 

declare@sqlvarchar(500)

 

set@sql='select姓名'

 

select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'

 

from(selectdistinct课程fromtb)a--同from tb group by课程，默认按课程名排序

 

set@sql=@sql+' from tb group by姓名'

 

exec(@sql)

 

  

 

--使用isnull(),变量先确定动态部分

 

declare@sqlvarchar(8000)

 

select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'

 

from(selectdistinct课程fromtb)asa      

 

set@sql='select姓名,'+@sql+' from tb group by姓名'

 

exec(@sql)

姓名       数学        物理        语文

 

---------- ----------- ----------- -----------

 

李四        84          94          74

 

张三        83          93          74

 

  

 

4、使用SQL Server 2005静态SQL

 

select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a

 

  

 

5、使用SQL Server 2005动态SQL

 

--使用stuff()

 

declare@sqlvarchar(8000)

 

set@sql=''  --初始化变量@sql

 

select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值

 

set@sql=stuff(@sql,1,1,'')--去掉首个','

 

set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'

exec(@sql)

 

  

 

--或使用isnull()

 

declare@sqlvarchar(8000)

 

–-获得课程集合

 

select@sql=isnull(@sql+',','')+课程fromtbgroupby课程           

 

set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'

 

exec(@sql)