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.