Pivot query

Question

Post reply

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 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?

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 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?

Can anyone help with a tip or two?

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

Lynn Pettis

SSC Guru

Points: 442447

August 3, 2012 at 10:45 am

#1521524

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: 442447 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.