Split Sql Server Change this Query Pivot

Posted by Jayakumars under ASP.NET AJAX on 3/20/2013 | Points: 10 | Views : 1467 | Status : [Member] [MVP] | Replies : 1
hi
http://www.dotnetspider.com/forum/ViewForum.aspx?ForumId=266904


My Query like this

My Data Like this

Table Field

Department
Software,Hardware,Supporting,Analysis,Biiling,Reporting

I need Output

Department1 Department2 Department3 Department4 Department5 Department6
Software Hardware Supporting Analysis Biiling Reporting

Mark as Answer if its helpful to you


Responses

Posted by: Saranpselvam on: 5/8/2013 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response