STEP1 : Create A Temp Table
create table ##sales(Department varchar(20),
Software varchar(20),Hardware varchar(20),Supporting varchar(20),Analysis varchar(20),Biiling varchar(20),Reporting varchar(20))
Step 2: Insert The Following Values
insert into ##sales values('DEP1',' xp',' mouse','yes ','A',' 100 ','YES')
insert into ##sales values('DEP2',' win7',' mouse ','yes',' A',' 100',' YES')
insert into ##sales values('DEP3',' photosho',' mouse','yes',' A',' 100 ','YES')
insert into ##sales values('DEP4',' photosho',' HDD',' yes ','A',' 100',' YES')
insert into ##sales values('DEP5',' MSoffice',' HDD',' yes ','A',' 100 ','YES')
insert into ##sales values('DEP6',' flash',' HDD',' yes ','A',' 100 ','YES')
insert into ##sales values('DEP1',' flash',' HDD',' yes',' A',' 300 ','YES')
insert into ##sales values('DEP1',' flash',' HDD',' yes',' A',' 500 ','YES')
insert into ##sales values('DEP6',' flash',' HDD',' yes ','A',' 300 ','YES')
step 3 ; write Pivot Query
select DEP1,DEP2,DEP3,DEP4,DEP5,DEP6,Software ,Hardware ,Supporting ,Analysis ,Reporting from
(
select Department ,Software ,Hardware ,Supporting ,Analysis ,Biiling ,Reporting from ##sales
) as A
pivot
(
max(Biiling)for department in(DEP1,DEP2,DEP3,DEP4,DEP5,DEP6)
) as B
Result:
DEP1 DEP2 DEP3 DEP4 DEP5 DEP6 Software Hardware Supporting Analysis Reporting
NULL NULL NULL NULL NULL 300 flash HDD yes A YES
500 NULL NULL NULL NULL 300 flash HDD yes A YES
NULL NULL NULL NULL 100 NULL MSoffice HDD yes A YES
NULL NULL NULL 100 NULL NULL photosho HDD yes A YES
NULL NULL 100 NULL NULL NULL photosho mouse yes A YES
NULL 100 NULL NULL NULL NULL win7 mouse yes A YES
100 NULL NULL NULL NULL NULL xp mouse yes A YES
Thanks
saravpselvam@gmail.com
Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator