表格样式转化 类似于行列转化
表1：
表2：
想由表1转化为表2
[最优解释]
select *
from (select 项目分类,年月,工时 from TB) as a
pivot(sum(工时) for 年月 in ([2012/01],[2012/02],...,[2012/12]) as b ---把...补全
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
整理人：中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/
------------------------------------------------------
go
--加上总成绩(学科平均分)
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))
生成动态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
生成静态:
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/
go
--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
--2000:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]
go
--2005:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
(8 行受影响)
*/
--应有尽有啊
if OBJECT_ID('Test') is not null drop table Test
create table Test
(年 int,年月 nvarchar(20),项目分类 nvarchar(20),工时 decimal(18,8)
)
go
--就列了3个月的数据（话说楼主，数据放上来有利于更好的让大家共同解决问题-_-!）
insert into Test(年,年月,项目分类,工时)
select '2012','2012/5','内销一类发动机',52 union all
select '2012','2012/5','综合',52 union all
select '2012','2012/6','返销发动机',25.08 union all
select '2012','2012/6','内销自产发动机',17628.069 union all
select '2012','2012/6','内销一类发动机',21329.27 union all
select '2012','2012/6','综合',38982.419 union all
select '2012','2012/7','返销发动机',384.72 union all
select '2012','2012/7','返销自产发动机',636.9285 union all
select '2012','2012/7','内销自产发动机',42784.112 union all
select '2012','2012/7','内销一类发动机',57295.82 union all
select '2012','2012/7','综合',101101.5805
if OBJECT_ID('dbo.ClearZero') is not null drop function dbo.ClearZero
go
--去除小数点后的无效0（网上抄的）
CREATE function dbo.ClearZero(@inValue varchar(50))
returns varchar(50)
as
begin
declare @returnValue varchar(20)
if(@inValue='')
set @returnValue='' --空的时候为空
else if (charindex('.',@inValue) ='0')
set @returnValue=@inValue --针对不含小数点的
else if ( substring(reverse(@inValue),patindex('%[^0]%',reverse(@inValue)),1)='.')
set @returnValue =left(@inValue,len(@inValue)-patindex('%[^0]%',reverse(@inValue))) --针对小数点后全是0的
else
set @returnValue =left(@inValue,len(@inValue)- patindex('%[^0]%.%',reverse(@inValue))+1) --其他任何情形
return @returnValue
end
go
--查询
select ltrim([年])+ltrim(项目分类) as 项目分类,dbo.ClearZero(ltrim([2012/5]))[2012/5],dbo.ClearZero(ltrim([2012/6]))[2012/6],dbo.ClearZero(ltrim([2012/7]))[2012/7] from Test
pivot (sum(工时) for 年月 in([2012/5],[2012/6],[2012/7]))
t
/*
项目分类 2012/5 2012/6 2012/7
2012返销发动机NULL25.08384.72
2012返销自产发动机NULLNULL636.9285
2012内销一类发动机5221329.2757295.82
2012内销自产发动机NULL17628.06942784.112
2012综合5238982.419101101.5805
*/
;WITH c1(年, 年月, 项目分类, 工时) AS
(
SELECT 2012, '2012/05', '内销一类发动机', 52 union all
SELECT 2012, '2012/05', '综合', 52 union all
SELECT 2012, '2012/06', '返销发动机', 25.08 union all
SELECT 2012, '2012/06', '内销自产发动机', 17628.069 union all
SELECT 2012, '2012/06', '内销一类发动机', 21329.27 union all
SELECT 2012, '2012/06', '综合', 38982.419 union all
SELECT 2012, '2012/07', '返销发动机', 384.72 union all
SELECT 2012, '2012/07', '返销自产发动机', 636.9285 union all
SELECT 2012, '2012/07', '内销自产发动机', 42784.112 union all
SELECT 2012, '2012/07', '内销一类发动机', 57295.82 union all
SELECT 2012, '2012/07', '综合', 101101.5805
)
, c2 as
(
SELECT
年,
DATEPART(month, CONVERT(datetime, 年月+'/01')) 月, --字符串+'/01'转换为DateTime
项目分类,
工时
FROM c1
)
SELECT
项目分类,
SUM(CASE WHEN 项目分类 = '内销一类发动机' AND 月 = 5 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销二类发动机' AND 月 = 5 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销自产发动机' AND 月 = 5 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销发动机' AND 月 = 5 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销自产发动机' AND 月 = 5 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '综合' AND 月 = 5 AND 年 = 2012 THEN 工时
ELSE NULL
END) [5yue] ,
SUM(CASE WHEN 项目分类 = '内销一类发动机' AND 月 = 6 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销二类发动机' AND 月 = 6 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销自产发动机' AND 月 = 6 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销发动机' AND 月 = 6 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销自产发动机' AND 月 = 6 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '综合' AND 月 = 6 AND 年 = 2012 THEN 工时
ELSE NULL
END) [6yue] ,
SUM(CASE WHEN 项目分类 = '内销一类发动机' AND 月 = 7 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销二类发动机' AND 月 = 7 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '内销自产发动机' AND 月 = 7 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销发动机' AND 月 = 7 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '返销自产发动机' AND 月 = 7 AND 年 = 2012 THEN 工时
WHEN 项目分类 = '综合' AND 月 = 7 AND 年 = 2012 THEN 工时
ELSE NULL
END) [7yue]
-----后面可以依次加上8月9月。。。。。
FROM c2
GROUP BY 项目分类
项目分类 5yue 6yue 7yue
-------------- --------------------------------------- --------------------------------------- ---------------------------------------
返销发动机 NULL 25.0800 384.7200
返销自产发动机 NULL NULL 636.9285
内销一类发动机 52.0000 21329.2700 57295.8200
内销自产发动机 NULL 17628.0690 42784.1120
综合 52.0000 38982.4190 101101.5805
警告: 聚合或其他 SET 操作消除了空值。
(5 行受影响)
