问题描述
create table CTC123 (master_ou_code int ,emp_code varchar(50),element_Code varchar(50),amount money, created_date datetime )
go
set nocount on
insert into CTC123 values('2',01234,'BASIC',5000,GETDATE())
insert into CTC123 values('2',01234,'HRA',3000,GETDATE())
insert into CTC123 values('2',01234,'DEPRE',6000,GETDATE())
insert into CTC123 values('2',01234,'WMA',4000,GETDATE())
insert into CTC123 values('2',01234,'WSAR',2000,GETDATE())
insert into CTC123 values('2',01234,'CNVAL',8000,GETDATE())
insert into CTC123 values('2',01235,'BASIC',5000,GETDATE())
insert into CTC123 values('2',01235,'HRA',3000,GETDATE())
insert into CTC123 values('2',01235,'DEPRE',6000,GETDATE())
insert into CTC123 values('2',01235,'INSNP',4000,GETDATE())
insert into CTC123 values('2',01235,'HNBL',2000,GETDATE())
insert into CTC123 values('2',01235,'CNVAL',8000,GETDATE())
insert into CTC123 values('2',01236,'BASIC',5000,GETDATE())
insert into CTC123 values('2',01236,'HRA',3000,GETDATE())
insert into CTC123 values('2',01236,'DEPRE',6000,GETDATE())
insert into CTC123 values('2',01236,'WMA',4000,GETDATE())
insert into CTC123 values('2',01236,'WSAR',2000,GETDATE())
insert into CTC123 values('2',01236,'CNVAL',8000,GETDATE())
insert into CTC123 values('2',01237,'BASIC',5000,GETDATE())
insert into CTC123 values('2',01237,'KAR',3000,GETDATE())
insert into CTC123 values('2',01237,'DEPRE',6000,GETDATE())
insert into CTC123 values('2',01237,'WMA',4000,GETDATE())
insert into CTC123 values('2',01237,'WSAR',2000,GETDATE())
insert into CTC123 values('2',01237,'CNVAL',8000,GETDATE())
set nocount off
go
select * from CTC123
如果我使用
--------------
if i use
--------------
DECLARE @ecList varchar(MAX) -- this will be the list of element_Code values
DECLARE @dynamicSQL varchar(MAX) -- this will eventually be the sql to run
-- This will create a list of element_Code values separated by commas
SELECT @ecList = COALESCE(@ecList + '','', '''') + element_Code
FROM (SELECT DISTINCT element_Code from CTC123) T
-- Build your sql by inserting the list you created above
SET @dynamicSQL = ''select * from (
Select emp_code,element_Code,amount from CTC123
) AS s
PIVOT (sum(amount) for element_Code in (''+ @ecList +'')) AS d''
-- run the sql
EXEC(@dynamicSQL)
--------------
EMP_CODE基本ZNVAL DEPRE HNBL HRA INSNP KAR WMA WSAR
1234 5000 8000 6000(空)3000(空)(空)4000 2000
1235 5000 8000 6000 2000 3000 4000(null)(null)(null)
1236 5000 8000 6000(空)3000(空)(空)4000 2000
1237 5000 8000 6000(null)(null)(null)3000 4000 2000
在上述结果中，我需要将null值设置为零，并且还需要为每个emp_code计算新Coloumn中的Total.
--------------
EMP_CODE BASICZNVALDEPREHNBLHRAINSNPKARWMAWSAR
1234 5000 8000 6000 (null) 3000 (null)(null) 4000 2000
1235 5000 8000 6000 2000 3000 4000 (null) (null) (null)
1236 5000 8000 6000 (null) 3000 (null) (null) 4000 2000
1237 5000 8000 6000 (null) (null) (null) 3000 4000 2000
in the above result i need null value to be Zero and also i need to calculate the Total in the new Coloumn for each emp_code.
推荐答案
DECLARE @ecList varchar(MAX) -- this will be the list of element_Code values
DECLARE @isnullselect varchar(MAX)
DECLARE @dynamicSQL varchar(MAX) -- this will eventually be the sql to run
-- This will create a list of element_Code values separated by commas
SELECT @ecList = COALESCE(@ecList + ',', '') + element_Code
FROM (SELECT DISTINCT element_Code from CTC123) T
-- This is building up the list of what we want to select
SELECT @isnullselect = COALESCE(@isnullselect + ',', '') + 'isnull(' + element_Code + ',0) as ' + element_Code
FROM (SELECT DISTINCT element_Code from CTC123) T
SELECT @isnullselect ='emp_code,' + @isnullselect
-- Build your sql by inserting the list you created above
SET @dynamicSQL = 'select ' + @isnullselect + ' from (
Select emp_code,element_Code,amount from CTC123
) AS s
PIVOT (sum(amount) for element_Code in ('+ @ecList +')) AS d'
-- run the sql
exec(@dynamicSQL)
请注意，新变量@isnullselect的构建方式与原始逗号分隔列表相同，并替换了select语句中的*.
最终的SQL看起来像这样...
Notice the new variable @isnullselect which is being built the same way as the original comma separated list and replaces the * in the select statement.
The final SQL looks like this ...
select emp_code,isnull(BASIC,0) as BASIC,isnull(CNVAL,0) as CNVAL,isnull(DEPRE,0) as DEPRE,isnull(HNBL,0) as HNBL,isnull(HRA,0) as HRA,isnull(INSNP,0) as INSNP,isnull(KAR,0) as KAR,isnull(WMA,0) as WMA,isnull(WSAR,0) as WSAR from ( Select emp_code,element_Code,amount from CTC123 ) AS s PIVOT (sum(amount) for element_Code in (BASIC,CNVAL,DEPRE,HNBL,HRA,INSNP,KAR,WMA,WSAR)) AS d
建立一个字符串，以与上述处理ISNULL完全相同的方式添加所有动态确定的值-但这一次将逗号(，)换成加号(+)
Build up a string that adds all the dynamically determined values in exactly the same way that we handled ISNULL above - but this time swap the comma (,) for a plus sign (+)
SELECT @rowTotal = COALESCE(@rowTotal + ''+'', '''') + ''isnull('' + element_Code + '',0)''
FROM (SELECT DISTINCT element_Code from CTC123) T
SELECT @rowTotal = @rowTotal + '' AS ROWTOTAL''
然后通过插入上面创建的所有列表(包括最后的rowTotal)来构建sql
Then build your sql by inserting all of the lists you created above, including the rowTotal at the end
SET @dynamicSQL = 'select ' + @isnullselect + ',' + @rowTotal + '
from (
Select emp_code,element_Code,amount from CTC123
) AS s
PIVOT (sum(amount) for element_Code in ('+ @ecList +')) AS d'
DECLARE @ecList varchar(MAX) -- this will be the list of element_Code values
DECLARE @isnullselect varchar(MAX)
DECLARE @dynamicSQL varchar(MAX) -- this will eventually be the sql to run
DECLARE @isnullselectTotal varchar(MAX)
-- This will create a list of element_Code values separated by commas
SELECT @ecList = COALESCE(@ecList + ',', '') + element_Code
FROM (SELECT DISTINCT element_Code from CTC123) T
-- This is building up the list of what we want to select
SELECT @isnullselect = COALESCE(@isnullselect + ',', '') + 'isnull(' + element_Code + ',0) as ' + element_Code
FROM (SELECT DISTINCT element_Code from CTC123) T
SELECT @isnullselectTotal = COALESCE(@isnullselectTotal + '+', '') + 'isnull(' + element_Code + ',0)'
FROM (SELECT DISTINCT element_Code from CTC123) T
SELECT @isnullselect ='emp_code,' + @isnullselect
-- Build your sql by inserting the list you created above
SET @dynamicSQL = 'select ' + @isnullselect +','+@isnullselectTotal +'Total'+ ' from (
Select emp_code,element_Code,amount from CTC123
) AS s
PIVOT (sum(amount) for element_Code in ('+ @ecList +')) AS d'
-- run the sql
exec(@dynamicSQL)
最终结果SQL
Final Result SQL
select Emp_code,isnull(BASIC,0) as BASIC,isnull(CNVAL,0) as CNVAL,isnull(DEPRE,0) as DEPRE,
isnull(HNBL,0) as HNBL,isnull(HRA,0) as HRA,isnull(INSNP,0) as INSNP,isnull(KAR,0) as KAR,
isnull(WMA,0) as WMA,isnull(WSAR,0) as WSAR,
(isnull(BASIC,0)+isnull(CNVAL,0)+isnull(DEPRE,0) +
isnull(HNBL,0)+isnull(HRA,0)+isnull(INSNP,0)+isnull(KAR,0) +
isnull(WMA,0) +isnull(WSAR,0)) as Total
from ( Select emp_code,element_Code,amount from CTC123 ) AS s PIVOT (sum(amount) for element_Code in (BASIC,CNVAL,DEPRE,HNBL,HRA,INSNP,KAR,WMA,WSAR)) AS d
这篇关于如何解决这个PIVOT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！