--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([姓名] nvarchar(22),[事项] nvarchar(21)) Insert #T select N'张三',N'A' union all select N'张三',N'B' union all select N'张三',N'C' union all select N'李四',N'A' union all select N'李四',N'B' union all select N'王五',N'A' Go --测试数据结束 SELECT 姓名 , MAX(CASE WHEN 事项 = 'A' THEN 事项 ELSE '' END) AS 事项1, MAX(CASE WHEN 事项 = 'B' THEN 事项 ELSE '' END) AS 事项2, MAX(CASE WHEN 事项 = 'C' THEN 事项 ELSE '' END) AS 事项3 FROM #T GROUP BY 姓名
if object_id('tb')is not null drop table tb Go create table tb(姓名 varchar(10),课程 varchar(10),分数 int) insert into tb values('张三','语文',74) insert into tb values('张三','数学',83) insert into tb values('张三','物理',93) insert into tb values('李四','语文',74) insert into tb values('李四','数学',84) insert into tb values('李四','物理',94) go select * from tb --------------------------------结果------------------------------------------------------------------------------------ 姓名 课程 分数 ---------- ---------- ----------- 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94(6 行受影响) 现在的问题是:我想根据姓名统计这个人的三门成绩,即:姓名 语文 数学 物理 首先看看使用case when end结构的时候:select 姓名, max(case 课程 when '语文' then 分数 else 0 end)语文, max(case 课程 when '数学'then 分数 else 0 end)数学, max(case 课程 when '物理'then 分数 else 0 end)物理 from tb group by 姓名 --------------------------------结果------------------------------------------------------------------------------------ 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94 张三 74 83 93(2 行受影响)这个结果就是我们想要的,然后再看看使用pivot:select * from tb pivot(max(分数) for 课程 in (语文,数学,物理))a --------------------------------结果------------------------------------------------------------------------------------ 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94 张三 74 83 93(2 行受影响)
if not object_id(N'Tempdb..#Tmp_Data') is null drop table #Tmp_Data Go Create table #Tmp_Data([姓名] nvarchar(22),[事项] nvarchar(21)) Insert #Tmp_Data select N'张三',N'A' union all select N'张三',N'B' union all select N'张三',N'C' union all select N'李四',N'A' union all select N'李四',N'B' union all select N'王五',N'A' Go DECLARE @sql NVARCHAR(max)='' DECLARE @i VARCHAR(3) SELECT TOP 1 @i=count(*) FROM #Tmp_Data GROUP BY [姓名] ORDER BY COUNT(*) DESCWHILE @i>0 SELECT @sql=',MAX(CASE WHEN RN='+@i+' THEN [事项] ELSE '''' END) as 事项'+@i+@sql,@i-=1
EXEC('SELECT [姓名]'+@sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [姓名] ORDER BY [姓名]) AS RN FROM #Tmp_Data) AS T GROUP BY [姓名]')
3楼,楼主看看case语法就明白了
Pivot语句主要用于行列转置和交叉统计使用,非常好用 代码如下: create table #data1 ([姓名] varchar(8), [事项] varchar(8) );goinsert into #data1([姓名],[事项]) select '张三','A' union all select '张三','B' union all select '张三','C' union all select '李四','A' union all select '李四','B' union all select '王五','A';goselect * ,ROW_NUMBER() over(partition by [姓名] order by [事项]) as rak into #data2 from #data1;goselect [姓名], ISNULL([1],null) as [事项1], ISNULL([2],null) as [事项2], ISNULL([3],null) as [事项3] from (select [姓名],[事项],rak from #data2) as [source1] pivot (max([事项]) for rak in ([1],[2],[3])) as [source2]go
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[事项] nvarchar(21))
Insert #T
select N'张三',N'A' union all
select N'张三',N'B' union all
select N'张三',N'C' union all
select N'李四',N'A' union all
select N'李四',N'B' union all
select N'王五',N'A'
Go
--测试数据结束
SELECT 姓名 ,
MAX(CASE WHEN 事项 = 'A' THEN 事项
ELSE ''
END) AS 事项1,
MAX(CASE WHEN 事项 = 'B' THEN 事项
ELSE ''
END) AS 事项2,
MAX(CASE WHEN 事项 = 'C' THEN 事项
ELSE ''
END) AS 事项3
FROM #T
GROUP BY 姓名
Go
create table tb(姓名 varchar(10),课程 varchar(10),分数 int)
insert into tb values('张三','语文',74)
insert into tb values('张三','数学',83)
insert into tb values('张三','物理',93)
insert into tb values('李四','语文',74)
insert into tb values('李四','数学',84)
insert into tb values('李四','物理',94)
go
select * from tb
--------------------------------结果------------------------------------------------------------------------------------
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94(6 行受影响)
现在的问题是:我想根据姓名统计这个人的三门成绩,即:姓名 语文 数学 物理
首先看看使用case when end结构的时候:select 姓名,
max(case 课程 when '语文' then 分数 else 0 end)语文,
max(case 课程 when '数学'then 分数 else 0 end)数学,
max(case 课程 when '物理'then 分数 else 0 end)物理
from tb
group by 姓名
--------------------------------结果------------------------------------------------------------------------------------
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93(2 行受影响)这个结果就是我们想要的,然后再看看使用pivot:select * from tb pivot(max(分数) for 课程 in (语文,数学,物理))a
--------------------------------结果------------------------------------------------------------------------------------
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93(2 行受影响)
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
Create table #Tmp_Data([姓名] nvarchar(22),[事项] nvarchar(21))
Insert #Tmp_Data
select N'张三',N'A' union all
select N'张三',N'B' union all
select N'张三',N'C' union all
select N'李四',N'A' union all
select N'李四',N'B' union all
select N'王五',N'A'
Go
DECLARE @sql NVARCHAR(max)=''
DECLARE @i VARCHAR(3)
SELECT TOP 1 @i=count(*) FROM #Tmp_Data GROUP BY [姓名] ORDER BY COUNT(*) DESCWHILE @i>0
SELECT @sql=',MAX(CASE WHEN RN='+@i+' THEN [事项] ELSE '''' END) as 事项'+@i+@sql,@i-=1
EXEC('SELECT [姓名]'+@sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [姓名] ORDER BY [姓名]) AS RN FROM #Tmp_Data) AS T GROUP BY [姓名]')
代码如下:
create table #data1
([姓名] varchar(8),
[事项] varchar(8)
);goinsert into #data1([姓名],[事项])
select '张三','A' union all
select '张三','B' union all
select '张三','C' union all
select '李四','A' union all
select '李四','B' union all
select '王五','A';goselect * ,ROW_NUMBER() over(partition by [姓名] order by [事项]) as rak
into #data2
from #data1;goselect [姓名],
ISNULL([1],null) as [事项1],
ISNULL([2],null) as [事项2],
ISNULL([3],null) as [事项3]
from
(select [姓名],[事项],rak from #data2) as [source1]
pivot
(max([事项]) for rak in ([1],[2],[3])) as [source2]go