原文链接已忘记,不好意思了。
试用sql关键字pivot与unpivot可以简便快捷实现数据的行列转换。废话不多说，直接上代码，关于它们的语法请自行msdn。
pivot：
创建表StudentScores
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentScores](
[UserName] [nvarchar](20) NULL,
[Subject] [nvarchar](30) NULL,
[Score] [float] NULL
) ON [PRIMARY]
GO
插入数据
insert into StudentScores values('Nick','语文',80)
insert into StudentScores values('Nick','数学',90)
insert into StudentScores values('Nick','英语',70)
insert into StudentScores values('Nick','生物',85)
insert into StudentScores values('Kent','语文',80)
insert into StudentScores values('Kent','数学',90)
insert into StudentScores values('Kent','英语',70)
表中数据如下图：
pivot sql语句如下：
select username,语文 as '语文',数学 as '数学',英语 as '英语',生物 as '生物'
from StudentScores
pivot(
max(score)
for [subject] in
(语文,数学,英语,生物)
)as t
一般sql语句：
select username,
max(case [subject] when '语文' then score end ) as '语文',
max(case [subject] when '数学' then score end ) as '数学',
max(case [subject] when '英语' then score end ) as '英语',
max(case [subject] when '生物' then score end ) as '生物'
from StudentScores
group by username
效果图如下：
unpivot：
unpivot语法与pivot一样
创建表StudentScores1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentScores1](
[姓名] [nvarchar](50) NOT NULL,
[语文] [int] NOT NULL,
[数学] [int] NOT NULL,
[英语] [int] NOT NULL,
[生物] [int] NOT NULL
) ON [PRIMARY]
GO
插入数据
insert into values('Nick',80,90,70,85)
insert into values('Kent',80,90,70,0)
表中数据如下图：
执行unpivot sql语句进行列转行如下：
select 姓名,学科,成绩
from StudentScores1
unpivot
(
成绩
for 学科 in
(语文,数学,英语,生物)
)as p
一般slq语句：
select 姓名,'语文' as 学科,max(语文)as 成绩 from
StudentScores1
group by 姓名
union all
select 姓名,'数学' as 学科,max(数学)as 成绩 from
StudentScores1
group by 姓名
union all
select 姓名,'英语' as 学科,max(英语)as 成绩 from
StudentScores1
group by 姓名
union all
select 姓名,'生物' as 学科,max(生物)as 成绩 from
StudentScores1
group by 姓名
效果图如下：
--动态行转列
declare @sql nvarchar(max)
select @sql=isnull(@sql+'],[','')+科目 from StudentScores1 group by 科目
select @sql='['+@sql+']'
select @sql='select 姓名,'+@sql+' from StudentScores1 a pivot (max(分數) for 科目 in('+@sql+')) as ads'
exec(@sql)