已有数据表如下：
id dt vl nt
---------------------------------------------
1234 2010-04-06 08:01:23.000 26.240 xyz
1234 2010-04-06 09:01:23.000 77.133 xyz
1234 2010-04-06 10:01:23.000 19.810 xyz
1234 2010-04-06 11:01:23.000 38.627 xyz
1234 2010-04-06 12:01:23.000 82.867 xyz
1234 2010-04-06 13:01:23.000 9.107 xyz
1234 2010-04-06 14:01:23.000 75.103 xyz
1234 2010-04-06 15:01:23.000 3.259 xyz
1235 2010-04-06 15:32:48.000 360.000 xyz
1235 2010-04-06 16:01:23.000 98.426 xyz
……按 datepart(hh,dt) 进行行列转换并加上最大最小和这个时间段的平均值
结果要：
id 8 9 10 …… max min avg
---------------------------------------------------
1234 26.24 77.133 19.81 360.0 3.259 *
id dt vl nt
---------------------------------------------
1234 2010-04-06 08:01:23.000 26.240 xyz
1234 2010-04-06 09:01:23.000 77.133 xyz
1234 2010-04-06 10:01:23.000 19.810 xyz
1234 2010-04-06 11:01:23.000 38.627 xyz
1234 2010-04-06 12:01:23.000 82.867 xyz
1234 2010-04-06 13:01:23.000 9.107 xyz
1234 2010-04-06 14:01:23.000 75.103 xyz
1234 2010-04-06 15:01:23.000 3.259 xyz
1235 2010-04-06 15:32:48.000 360.000 xyz
1235 2010-04-06 16:01:23.000 98.426 xyz
……按 datepart(hh,dt) 进行行列转换并加上最大最小和这个时间段的平均值
结果要：
id 8 9 10 …… max min avg
---------------------------------------------------
1234 26.24 77.133 19.81 360.0 3.259 *
---
select id,sum(case when datepart(hh,dt) = 8 then vl else 0 end) as [8],
sum(case when datepart(hh,dt) = 8 then vl else 0 end) as [9],
sum(case when datepart(hh,dt) = 8 then vl else 0 end) as [10],
max(val) ,min(vl),avg(vl)
from ta
group by id
*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/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--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
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 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------
select id ,
max(case datepart(hh,dt) when 0 then vl else 0 end) [0],
max(case datepart(hh,dt) when 1 then vl else 0 end) [1],
max(case datepart(hh,dt) when 2 then vl else 0 end) [2],
...
max(case datepart(hh,dt) when 23 then vl else 0 end) [23],
max(vl) [max],
min(vl) [min],
avg(vl) [avg]
from tb group by id
select id ,
max(case datepart(hh,dt) when 0 then vl else 0 end) [0],
max(case datepart(hh,dt) when 1 then vl else 0 end) [1],
max(case datepart(hh,dt) when 2 then vl else 0 end) [2],
...
max(case datepart(hh,dt) when 23 then vl else 0 end) [23],
max(vl) [max],
min(vl) [min],
avg(vl) [avg]
from tb group by id--动态的.
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case datepart(hh,dt) when ''' + cast(xs as varchar) + ''' then val else 0 end) [' + cast(xs as varchar) + ']'
from (select distinct datepart(hh,dt) xs from tb) as a
set @sql = @sql + ' ,max(vl) [max],min(vl) [min],avg(vl) [avg]from tb group by id'
exec(@sql)
insert into tb values(1234 , '2010-04-06 08:01:23.000' , 26.240 , 'xyz')
insert into tb values(1234 , '2010-04-06 09:01:23.000' , 77.133 , 'xyz')
insert into tb values(1234 , '2010-04-06 10:01:23.000' , 19.810 , 'xyz')
insert into tb values(1234 , '2010-04-06 11:01:23.000' , 38.627 , 'xyz')
insert into tb values(1234 , '2010-04-06 12:01:23.000' , 82.867 , 'xyz')
insert into tb values(1234 , '2010-04-06 13:01:23.000' , 9.107 , 'xyz')
insert into tb values(1234 , '2010-04-06 14:01:23.000' , 75.103 , 'xyz')
insert into tb values(1234 , '2010-04-06 15:01:23.000' , 3.259 , 'xyz')
insert into tb values(1235 , '2010-04-06 15:32:48.000' , 360.000 , 'xyz')
insert into tb values(1235 , '2010-04-06 16:01:23.000' , 98.426 , 'xyz')
go
--静态的.
select id ,
max(case datepart(hh,dt) when 0 then vl else 0 end) [0],
max(case datepart(hh,dt) when 1 then vl else 0 end) [1],
max(case datepart(hh,dt) when 2 then vl else 0 end) [2],
max(case datepart(hh,dt) when 3 then vl else 0 end) [3],
max(case datepart(hh,dt) when 4 then vl else 0 end) [4],
max(case datepart(hh,dt) when 5 then vl else 0 end) [5],
max(case datepart(hh,dt) when 6 then vl else 0 end) [6],
max(case datepart(hh,dt) when 7 then vl else 0 end) [7],
max(case datepart(hh,dt) when 8 then vl else 0 end) [8],
max(case datepart(hh,dt) when 9 then vl else 0 end) [9],
max(case datepart(hh,dt) when 10 then vl else 0 end) [10],
max(case datepart(hh,dt) when 11 then vl else 0 end) [11],
max(case datepart(hh,dt) when 12 then vl else 0 end) [12],
max(case datepart(hh,dt) when 13 then vl else 0 end) [13],
max(case datepart(hh,dt) when 14 then vl else 0 end) [14],
max(case datepart(hh,dt) when 15 then vl else 0 end) [15],
max(case datepart(hh,dt) when 16 then vl else 0 end) [16],
max(case datepart(hh,dt) when 17 then vl else 0 end) [17],
max(case datepart(hh,dt) when 18 then vl else 0 end) [18],
max(case datepart(hh,dt) when 19 then vl else 0 end) [19],
max(case datepart(hh,dt) when 20 then vl else 0 end) [20],
max(case datepart(hh,dt) when 21 then vl else 0 end) [21],
max(case datepart(hh,dt) when 22 then vl else 0 end) [22],
max(case datepart(hh,dt) when 23 then vl else 0 end) [23],
max(vl) [max],
min(vl) [min],
avg(vl) [avg]
from tb group by id
/*
id 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 max min avg
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1234 .000 .000 .000 .000 .000 .000 .000 .000 26.240 77.133 19.810 38.627 82.867 9.107 75.103 3.259 .000 .000 .000 .000 .000 .000 .000 .000 82.867 3.259 41.518250
1235 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 360.000 98.426 .000 .000 .000 .000 .000 .000 .000 360.000 98.426 229.213000（所影响的行数为 2 行）*/--动态的.
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case datepart(hh,dt) when ''' + cast(xs as varchar) + ''' then vl else 0 end) [' + cast(xs as varchar) + ']'
from (select distinct datepart(hh,dt) xs from tb) as a
set @sql = @sql + ' ,max(vl) [max],min(vl) [min],avg(vl) [avg]from tb group by id'
exec(@sql)
/*
id 8 9 10 11 12 13 14 15 16 max min avg
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1234 26.240 77.133 19.810 38.627 82.867 9.107 75.103 3.259 .000 82.867 3.259 41.518250
1235 .000 .000 .000 .000 .000 .000 .000 360.000 98.426 360.000 98.426 229.213000*/drop table tb
--
-- 使用过SQL Server 2000的人都知道，要想实现行列转换,必须综合利用聚合函数和动态SQL，具体实现起来需要一定的技巧，而在SQL Server 2005中，使用新引进的关键字PIVOT/UNPIVOT，则可以很容易的实现行列转换的需求。
--
--在本文中我们将通过两个简单的例子详细讲解PIVOT/UNPIVOT的用法。
--
--PIVOT的用法：
--
--首先创建测试表，然后插入测试数据create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test
--id name quarter profile
----------- -------------- ----------- -----------
--1 a 1 1000
--1 a 2 2000
--1 a 3 4000
--1 a 4 5000
--2 b 1 3000
--2 b 2 3500
--2 b 3 4200
--2 b 4 5500
--(8 row(s) affected)
--使用PIVOT将四个季度的利润转换成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
--id name 一季度 二季度 三季度 四季度
-------- --------- ----------- -------- ------- -------
--1 a 1000 2000 4000 5000
--2 b 3000 3500 4200 5500
--(2 row(s) affected)
--UNPIVOT的用法： --首先建立测试表，然后插入测试数据
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)
select * from test
--id name Q1 Q2 Q3 Q4
-------- ------- --------- --------- -------- --------
--1 a 1000 2000 4000 5000
--2 b 3000 3500 4200 5500
--(2 row(s) affected)
--使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
--id name quarter profile
----------- ----------- ---------- -----------
--1 a Q1 1000
--1 a Q2 2000
--1 a Q3 4000
--1 a Q4 5000
--2 b Q1 3000
--2 b Q2 3500
--2 b Q3 4200
--2 b Q4 5500
--(8 row(s) affected)
记住四个关键字就搞定了
case when 和 unon all
剩下的就是慢慢地做了
-- Author : htl258(Tony)
-- Date : 2010-04-06 18:54:30
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE TB(ID INT,DT DATETIME,VL DECIMAL(18,3),NT VARCHAR(10))
INSERT INTO TB VALUES(1234 , '2010-04-06 08:01:23.000' , 26.240 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 09:01:23.000' , 77.133 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 10:01:23.000' , 19.810 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 11:01:23.000' , 38.627 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 12:01:23.000' , 82.867 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 13:01:23.000' , 9.107 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 14:01:23.000' , 75.103 , 'XYZ')
INSERT INTO TB VALUES(1234 , '2010-04-06 15:01:23.000' , 3.259 , 'XYZ')
INSERT INTO TB VALUES(1235 , '2010-04-06 15:32:48.000' , 360.000 , 'XYZ')
INSERT INTO TB VALUES(1235 , '2010-04-06 16:01:23.000' , 98.426 , 'XYZ')
GOselect id,ISNULL([0],0) [0],ISNULL([1],0) [1],ISNULL([2],0) [2],ISNULL([3],0) [3],ISNULL([4],0) [4],ISNULL([5],0) [5],
ISNULL([6],0) [6],ISNULL([7],0) [7],ISNULL([8],0) [8],ISNULL([9],0) [9],ISNULL([10],0) [10],ISNULL([11],0) [11],
ISNULL([12],0) [12],ISNULL([13],0) [13],ISNULL([14],0) [14],ISNULL([15],0) [15],ISNULL([16],0) [16],ISNULL([17],0) [17],
ISNULL([18],0) [18],ISNULL([19],0) [19],ISNULL([20],0) [20],ISNULL([21],0) [21],ISNULL([22],0) [22],ISNULL([23],0) [23],
maxvl,minvl,avgvl
from (
select ID,DATEPART(hh,dt) dt,vl,
MAX(vl)over(partition by id) maxvl,
MIN(vl)over(partition by id) minvl,
AVG(vl)over(partition by id) avgvl
from tb
) t
pivot(max(vl) for dt in(
[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],
[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) b
/*
id 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 maxvl minvl avgvl
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1234 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 26.240 77.133 19.810 38.627 82.867 9.107 75.103 3.259 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 82.867 3.259 41.518250
1235 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 360.000 98.426 0.000 0.000 0.000 0.000 0.000 0.000 0.000 360.000 98.426 229.213000(2 行受影响)
*/顺便写个05的