CREATE TABLE [dbo].[tblHrkq](
[年度] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[期间] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[员工编号] [char](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[员工姓名] [char](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[应出合计] [numeric](18, 0) NULL,
[实出合计] [numeric](18, 0) NULL
) ON [PRIMARY]GO
INSERT INTO [dbo].[tblHrkq]
SELECT '2009','01','00001','张三',22,22
UNION
SELECT '2009','02','00001','张三',21,21
UNION
SELECT '2010','01','00001','张三',24,24
UNION
SELECT '2010','02','00001','张三',21,21
SET ANSI_PADDING OFF
-------------------------------------------------------------
--要求得到[注:]当然select case 语句能实现,但 povit 如何实现
select 员工编号,员工姓名,[2009] as Y_2009,[2010] as Y_2010
from
(
select 员工编号,员工姓名,年度 as Year_Order,应出合计 from tblhrkq
) t
pivot
(sum(应出合计)for Year_Order in ([2009],[2010])) pvt
-------------------------------------------------------------
员工编号 员工姓名 2009应出合计 2009实出合计 2010应出合计 2010实出合计
00001 张三
[年度] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[期间] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[员工编号] [char](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[员工姓名] [char](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[应出合计] [numeric](18, 0) NULL,
[实出合计] [numeric](18, 0) NULL
) ON [PRIMARY]GO
INSERT INTO [dbo].[tblHrkq]
SELECT '2009','01','00001','张三',22,22
UNION
SELECT '2009','02','00001','张三',21,21
UNION
SELECT '2010','01','00001','张三',24,24
UNION
SELECT '2010','02','00001','张三',21,21
SET ANSI_PADDING OFF
-------------------------------------------------------------
--要求得到[注:]当然select case 语句能实现,但 povit 如何实现
select 员工编号,员工姓名,[2009] as Y_2009,[2010] as Y_2010
from
(
select 员工编号,员工姓名,年度 as Year_Order,应出合计 from tblhrkq
) t
pivot
(sum(应出合计)for Year_Order in ([2009],[2010])) pvt
-------------------------------------------------------------
员工编号 员工姓名 2009应出合计 2009实出合计 2010应出合计 2010实出合计
00001 张三
max(a.[2010应出合计])[2010应出合计],
max(a.[2009实出合计])[2009实出合计],
max(a.[2010实出合计])[2010实出合计]
from (select 员工编号,员工姓名,
case 年度 when '2009' then sum(应出合计) else 0 end as '2009应出合计' ,
case 年度 when '2010' then sum(应出合计) else 0 end as '2010应出合计',
case 年度 when '2009' then sum(实出合计) else 0 end as '2009实出合计',
case 年度 when '2010' then sum(实出合计) else 0 end as '2010实出合计'
from [dbo].[tblHrkq]
group by 员工编号,员工姓名,年度) a
group by a.员工编号,a.员工姓名
--这是用case语句实现的,还算凑合吧
pivot 只能把某一列转化成行吗,不能对两列转化成行吗
SELECT 员工编号, 员工姓名,
SUM(CASE 年度 WHEN '2009' THEN 应出合计 ELSE 0 END) AS '2009应出合计',
SUM(CASE 年度 WHEN '2010' THEN 应出合计 ELSE 0 END) AS '2010应出合计',
SUM(CASE 年度 WHEN '2009' THEN 实出合计 ELSE 0 END) AS '2009实出合计',
SUM(CASE 年度 WHEN '2010' THEN 实出合计 ELSE 0 END) AS '2010实出合计'
FROM [dbo].[tblHrkq]
GROUP BY 员工编号, 员工姓名这样就可以了
DECLARE @COL VARCHAR(200)
set @COL=stuff((SELECT ','+ QUOTENAME(rtrim([年度])+'应出合计')+','+QUOTENAME(rtrim([年度])+'实出合计')
FROM [tblHrkq]
group by [年度]
order by [年度]
for xml path('')),1,1,'')
exec (
'
select 员工编号 ,员工姓名,'+@COL+' from (
SELECT rtrim([年度])+TYPE as TYPE,员工编号,员工姓名,value FROM [tblHrkq]
UNPIVOT
( VALUE FOR TYPE IN([实出合计],[应出合计])) AS UNPVT
) a
pivot (sum(value) for TYPE in(' +@COL+')) pvt'
)
员工编号 员工姓名 2009应出合计 2009实出合计 2010应出合计 2010实出合计
-------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
00001 张三 43 43 45 45(1 行受影响)