How to Solve this PIVOT ?

Question

0.00/5 (No votes)

See more:

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
--------------

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 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.

Posted 4-Jul-14 0:25am

Kannan 261190

Add a Solution

Comments

CHill60 4-Jul-14 6:50am

Total of what?

Kannan 261190 4-Jul-14 6:58am

ex:
1234 = 5000+8000+6000+0+3000+0+0+4000+2000=28000, this amount shouhd come in new Coloumn as [Total]

CHill60 4-Jul-14 7:03am

Ok ... It's similar to how I've got rid of the nulls in solution 1 ... I'll update it in a minute for the total

CHill60 4-Jul-14 7:14am

The bit that is missing from this post is that the list of columns BASIC, CNVAL, DEPRE etc is not known until run-time - it's generated dynamically - see my solution to this and OPs previous question

3 solutions

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 · Answer 1 · 2014-07-04T00:55:00

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

SQL

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)

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 ...

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 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

[Edit - corrected the missing column names]

[Edit #2 - put a row total column on the end]
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'

Then build your sql by inserting all of the lists you created above, including the rowTotal at the end

SQL

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'

Murugesan22 · Answer 2 · 2014-07-04T01:32:00

Try this

SQL

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)

Final Result SQL

VB

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

Manjeet349 · Answer 3 · 2014-07-04T01:49:00

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...

:)

How to Solve this PIVOT ?

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0