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

问题描述

我有一张桌子，其中一列我必须排成一行

例如：

I have a table in which one Column i have to make row
example:

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

select * from (

Select emp_code,element_Code,amount from CTC123

) as s

pivot (sum(amount) for element_Code in ([BASIC],[WMA])) as d

i将只获得BASIC和WMA，考虑有100个element_code，我们如何添加

element_Code in（） ）只需

i will get only BASIC and WMA , Consider there are 100 element_code and how can we add in
element_Code in ()) simply

推荐答案

假设可以使用动态SQL，您可以构建SQL语句以包含动态生成的逗号分隔的元素序列列表

Assuming it is ok to use dynamic SQL you can build your SQL statement to include a dynamically generated comma-separated list of element_Code

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)

信用Pinal Dave [ ^ ]以逗号分隔的列表



结果

With credit to Pinal Dave[^] for the comma-separated list

Results

EMP_CODE BASIC  CNVAL   DEPRE   HRA    WMA     WSAR
1234     5000   8000    6000    3000   4000    2000 
1237     (null) 8000   (null)   (null) (null)  (null) 

结果

[edit - should point out that I didn't load all of the data]
Results

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 

参见 HERE [ ^ ]。像魅力一样工作！

See HERE[^]. Works like a charm!

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