Pivot query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot query

Author

Topic

rabisco
Starting Member

15 Posts

Posted - 2012-08-06 : 15:50:55

I have the following data...



uid    fieldid   vaue
1172	5000	
1172	5001	    Development,support,management
1172	5002	
1172	5003	     Other
1172	5004	     Computing
1172	5004	     Medical
1172	5005	     Networking
1172	5005	     Consulting Projects
1172	5006	     Chicago
1172	5009

The following code...


select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]   
from
(select userid, fieldID, value from jiveuserprofile) jupf
PIVOT (min(value) for fieldid IN ([5001],[5004],[5005])) as jupftmp
where userid = '1172'
order by  userid

Returns the value Computing for the fieldid 5004. How do I get both values computing and medical for the fieldid 5004?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 16:03:39

[code]
;with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value
FROM jiveuserprofile
WHERE userid = u.userid
AND fieldID = u.fieldID
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
where userid = '1172'
order by userid

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rabisco
Starting Member

15 Posts

Posted - 2012-08-06 : 16:11:39

quote:

Originally posted by visakh16


;with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value 
FROM jiveuserprofile 
WHERE userid = u.userid 
AND fieldID = u.fieldID 
FOR XML PATH('')),1,1,'') 
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]   
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
where userid = '1172'
order by  userid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16, thanks. Just what I needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 16:40:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rabisco
Starting Member

15 Posts

Posted - 2012-08-06 : 17:32:17

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

One more question, I tried to create view with your solution...


create view as view1
as 
;with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value 
FROM jiveuserprofile 
WHERE userid = u.userid 
AND fieldID = u.fieldID 
FOR XML PATH('')),1,1,'') 
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]   
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
order by  userid
go

but I get the error...

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 18:30:52

the syntax is wrong. it should be



create view as view1
as 
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rabisco
Starting Member

15 Posts

Posted - 2012-08-07 : 10:09:54

quote:
Originally posted by visakh16

the syntax is wrong. it should be

create view as view1
as
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

With the corrected syntax


create view view1
as
 ;with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value 
FROM jiveuserprofile 
WHERE userid = u.userid 
AND fieldID = u.fieldID 
FOR XML PATH('')),1,1,'') 
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]   
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
order by  userid
go

I still get the error ...

Msg 102, Level 15, State 1, Procedure view1, Line 3
Incorrect syntax near ';'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 10:12:55

you dont require the ; and order by while used in view
it should be like below


create view view1
as
 with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value 
FROM jiveuserprofile 
WHERE userid = u.userid 
AND fieldID = u.fieldID 
FOR XML PATH('')),1,1,'') 
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]   
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rabisco
Starting Member

15 Posts

Posted - 2012-08-07 : 10:22:12

quote:
Originally posted by visakh16

you dont require the ; and order by while used in view
it should be like below

create view view1
as
with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value
FROM jiveuserprofile
WHERE userid = u.userid
AND fieldID = u.fieldID
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh16. Got it, it works. Can I vote for your anwser(s)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 10:23:51

quote:
Originally posted by rabisco

quote:
Originally posted by visakh16

you dont require the ; and order by while used in view
it should be like below

create view view1
as
with UserValues (userid,fieldid,valuelist)
AS
(
SELECT u.userid,u.fieldID,
STUFF((SELECT ',' + value
FROM jiveuserprofile
WHERE userid = u.userid
AND fieldID = u.fieldID
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT userid,fieldID from jiveuserprofile)u
)
select userid, [5001] as [expertise], [5004] as [industry], [5005] as [lookingfor]
from
(select userid, fieldID, valuelist from UserValues) jupf
PIVOT (min(valuelist) for fieldid IN ([5001],[5004],[5005])) as jupftmp
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh16. Got it, it works. Can I vote for your anwser(s)?

there's no voting concept in this site

you can just append title with SOLVED: if you want to mark this as complete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources