Pivot query

Question

Pivot query

rabisco

Ten Centuries

Points: 1032
More actions
August 2, 2012 at 11:32 am

#99721

I have the following data...
uid fieldid vaue
11725000
11725001 Development,support,management
11725002
11725003 Other
11725004 Computing
11725004 Medical
11725005 Networking
11725005 Consulting Projects
11725006 Chicago
11725009
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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rabisco Ten Centuries Points: 1032 More actions · Answer 1

rabisco (8/2/2012)
I have the following data...
uid fieldid vaue
11725000
11725001 Development,support,management
11725002
11725003 Other
11725004 Computing
11725004 Medical
11725005 Networking
11725005 Consulting Projects
11725006 Chicago
11725009
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?

Can anyone help with a tip or two?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You will need to flatten the data. Look at 5001, that is the same thing you will need to do for 5004 and 5005 if you want all the values. Currently you are getting just what you asked for, the min value.

rabisco Ten Centuries Points: 1032 More actions · Answer 3

Lynn Pettis (8/3/2012)
You will need to flatten the data. Look at 5001, that is the same thing you will need to do for 5004 and 5005 if you want all the values. Currently you are getting just what you asked for, the min value.

Thanks. Here's some more context; Fieldid 5001 is a freetext field in the UI so all it's data it written into one row. However for fieldid 5004 and 5005, multiple selections are possible from a list of options; and every option selected creates a new row.

So how would I flatten the data?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

rabisco (8/3/2012)
Lynn Pettis (8/3/2012)
You will need to flatten the data. Look at 5001, that is the same thing you will need to do for 5004 and 5005 if you want all the values. Currently you are getting just what you asked for, the min value.
Thanks. Here's some more context; Fieldid 5001 is a freetext field in the UI so all it's data it written into one row. However for fieldid 5004 and 5005, multiple selections are possible from a list of options; and every option selected creates a new row.
So how would I flatten the data?
Thanks.

You would need to concatenate the values in the value column for the same fieldid.

rabisco Ten Centuries Points: 1032 More actions · Answer 5

Lynn Pettis (8/3/2012)
rabisco (8/3/2012)
Lynn Pettis (8/3/2012)
You will need to flatten the data. Look at 5001, that is the same thing you will need to do for 5004 and 5005 if you want all the values. Currently you are getting just what you asked for, the min value.
Thanks. Here's some more context; Fieldid 5001 is a freetext field in the UI so all it's data it written into one row. However for fieldid 5004 and 5005, multiple selections are possible from a list of options; and every option selected creates a new row.
So how would I flatten the data?
Thanks.
You would need to concatenate the values in the value column for the same fieldid.

Thanks; I would appreciate some tips on how to concatenate the values in the value column for the same fieldid.

rVadim Hall of Fame Points: 3969 More actions · Answer 6

Check this article:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

--Vadim R.

rabisco Ten Centuries Points: 1032 More actions · Answer 7

rVadim (8/11/2012)
Check this article:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Thanks.