如何解决这个PIVOT? [英] How to Solve this PIVOT ?

问题描述

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 BASIC ZNVAL DEPRE HNBL HRA INSNP KAR WMA WSAR
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.

推荐答案

第1部分的解决方案等待回应...

ISNULL函数会将空值转换为零(或任何您需要的值).它比COALESCE函数略快(执行相同，但可以处理多列).

您的问题在于，它必须位于最终SELECT中的每一列上，并且您正在动态生成它-因此，您还需要动态生成sql的那一部分

Solution to part 1 while I wait for response ...

The ISNULL function will convert nulls to zero (or whatever you require). It is marginally faster than the COALESCE function (does the same but can handle multiple columns).

Your problem here is that it needs to be on each column in the final SELECT - and you are generating that dynamically - therefore you also need to generate that part of the sql dynamically

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'

尝试一下

Try this

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

我希望这会有所帮助:

-声明变量
DECLARE @ecListCol VARCHAR(MAX)-这将是列名称，作为您的element_code
DECLARE @Total VARCHAR(MAX)-这将是总数

-这应该比COALESCE更快.
SET @ecListCol =(SELECT DISTINCT''，ISNULL(''+ element_Code +''，0)AS''+ CTC123中的element_Code FOR XML PATH(''''))
SET @Total =(从CTC123 FOR XML PATH(''''))中选择DISTINCT''+ ISNULL(''+ element_Code +''，0)''

-从两个变量中首先删除
SET @ecListCol =右(@ ecListCol，LEN(@ecListCol)-1)
SET @Total = RIGHT(@ Total，LEN(@Total)-1)

-选择外部查询中的列
选择emp_code，''+ @ ecListCol +''，''+ @ Total +''AS Total from(
...
)

和您其余的查询是相同的...

:)

I hope this would help :

--Declare Variables
DECLARE @ecListCol VARCHAR(MAX) -- this will be the columns name as your element_code
DECLARE @Total VARCHAR(MAX) --this will be the total

--This should work faster than COALESCE.
SET @ecListCol =(SELECT DISTINCT '',ISNULL(''+element_Code +'',0) AS ''+element_Code from CTC123 FOR XML PATH(''''))
SET @Total =(SELECT DISTINCT ''+ISNULL(''+element_Code +'',0)'' from CTC123 FOR XML PATH(''''))

--to remove first , from both variable
SET @ecListCol = RIGHT(@ecListCol,LEN(@ecListCol)-1)
SET @Total = RIGHT(@Total,LEN(@Total)-1)

--select columns in your outer query
select emp_code,''+@ecListCol+'',''+@Total+'' AS Total from(
...
)

and rest of your query is same...

:)

这篇关于如何解决这个PIVOT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！