动态的
查询出的结果集
id name time score (字段)
1 张三 1月 7.5
1 张三 2月 8.5
1 张三 3月 9.5
2 李四 1月 7.1
2 李四 2月 5.5
2 李四 3月 7.5 如何将结果集转变成下面的格式呢 id name 1月 2月 3月 (字段)
1 张三 7.5 8.5 9.5
2 李四 7.1 5.5 7.5
查询出的结果集
id name time score (字段)
1 张三 1月 7.5
1 张三 2月 8.5
1 张三 3月 9.5
2 李四 1月 7.1
2 李四 2月 5.5
2 李四 3月 7.5 如何将结果集转变成下面的格式呢 id name 1月 2月 3月 (字段)
1 张三 7.5 8.5 9.5
2 李四 7.1 5.5 7.5
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-12 15:33:29
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] nvarchar(4),[time] nvarchar(4),[score] numeric(2,1))
insert [huang]
select 1,N'张三',N'1月',7.5 union all
select 1,N'张三',N'2月',8.5 union all
select 1,N'张三',N'3月',9.5 union all
select 2,N'李四',N'1月',7.1 union all
select 2,N'李四',N'2月',5.5 union all
select 2,N'李四',N'3月',7.5
--------------生成数据--------------------------declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([time])+N'=max(case when [time]=N'+quotename([time],'''')+N' then [Score] else 0.0 end)'
from [huang] group by [time]
EXEC('select [id],[NAME]'+@s+' from [huang] group by [id],[NAME]')
----------------结果----------------------------
/*
id NAME 1月 2月 3月
----------- ---- --------------------------------------- --------------------------------------- ---------------------------------------
1 张三 7.5 8.5 9.5
2 李四 7.1 5.5 7.5
*/
(
id int,
name varchar(20),
[time] varchar(5),
scoer decimal(6,1)
)
go
insert #tb
select 1,'张三','1月',7.5 union all
select 1,'张三','2月',8.5 union all
select 1,'张三','3月',9.5 union all
select 2,'李四','1月',7.1 union all
select 2,'李四','2月',5.5 union all
select 2,'李四','3月',7.5
goselect
* from #tb
pivot (max([scoer] )for [time] in([1月],[2月],[3月]))a
order by 1
/*
id name 1月 2月 3月
-----------------------------------
1 张三 7.5 8.5 9.5
2 李四 7.1 5.5 7.5
*/
1 张三 7.5 8.5 9.5 25.5
2 李四 7.1 5.5 7.5 20.1
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([time])+N'=max(case when [time]=N'+quotename([time],'''')+N' then [Score] else 0.0 end)'
from [huang] group by [time]
EXEC('select [id],[NAME]'+@s+',sum(score) as ''合计'' from [huang] group by [id],[NAME]')
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] nvarchar(4),[time] nvarchar(4),[score] numeric(2,1))
insert [huang]
select 1,N'张三',N'1月',7.5 union all
select 1,N'张三',N'2月',8.5 union all
select 1,N'张三',N'3月',9.5 union all
select 2,N'李四',N'1月',7.1 union all
select 2,N'李四',N'2月',5.5 union all
select 2,N'李四',N'3月',7.5
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([time])+N'=max(case when [time]=N'+quotename([time],'''')+N' then [Score] else 0.0 end)'
FROM(SELECT *,(SELECT SUM(score) FROM huang a WHERE a.NAME=huang.NAME)sums FROM [huang])huang group by [time]
EXEC('select [id],[NAME]'+@s+',sums 合计 from (SELECT *,(SELECT SUM(score) FROM huang a WHERE a.NAME=huang.NAME)sums FROM [huang])huang group by [id],[NAME],sums')
/*
id NAME 1月 2月 3月 合计
----------- ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 张三 7.5 8.5 9.5 25.5
2 李四 7.1 5.5 7.5 20.1*/
(
id int,
name varchar(20),
time varchar(5),
score decimal(6,1)
)
go
insert #tb
select 1,'张三','1月',7.5 union all
select 1,'张三','2月',8.5 union all
select 1,'张三','3月',9.5 union all
select 2,'李四','1月',7.1 union all
select 2,'李四','2月',5.5 union all
select 2,'李四','3月',7.5
go
declare @s nvarchar(4000)
set @s='select id,name'
select @s=@s + ',sum(case time when '''+time + ''' then score else 0 end) as [' +time+ ']'
from #tb group by time
set @s=@s + ', sum(score) as total from #tb group by id,name order by id '
exec(@s)
id name 1月 2月 3月 (字段)
1 张三 7.5 8.5 9.5
2 李四 7.1 5.5 7.5转换成 id name time score (字段)
1 张三 1月 7.5
1 张三 2月 8.5
1 张三 3月 9.5
2 李四 1月 7.1
2 李四 2月 5.5
2 李四 3月 7.5
union all
select id,name,'2月' as time,[2月] as score from tb
union all
select id,name,'3月' as time,[3月] as score from tb.........
一种,当需要转换的列的名称确定的时候,可以使用pivot 也就是3楼的方法;
假如列头无法确认,列头的变动的,例如日期做列头这样,这个就需要对列头进行动态的生成就需要拼凑SQL语句,可以参考7楼。
WITH a1 (id,name,[1月],[2月],[3月]) AS
(
SELECT 1,'张三',7.5,8.5,9.5 UNION ALL
SELECT 2,'李四',7.1,5.5,7.5
)
SELECT b.id,b.name,b.TIME,b.score
FROM a1 a
UNPIVOT (score FOR time IN ([1月],[2月],[3月])) b