How to Solve this PIVOT ?

Question

0.00/5 (No votes)

See more:

hi,

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

SQL

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

SQL

select * from (

Select emp_code,element_Code,amount from CTC123

) as s

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

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

Posted 2-Jul-14 6:31am

Kannan 261190

Add a Solution

2 solutions

Solution 2

See HERE[^]. Works like a charm!

Posted 2-Jul-14 8:26am

Herman<T>.Instance

Comments

Kannan 261190 4-Jul-14 4:31am

how to change null values to zero for the amounts for example around 500 employees are there
and we NEED 0 instead of NULL

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-07-02T07:08:00

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

SQL

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)

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