hi,
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 will get only BASIC and WMA , Consider there are 100 element_code and how can we add in
element_Code in ()) simply