Link to home
Create AccountLog in
Avatar of jxbma
jxbmaFlag for United States of America

asked on

How do I write a SQL query which aggregates FieldName/FieldValue pairs into a single column?

Hi:

I'm trying to figure out a SQL query.

Basically, I have a View which looks like this:
Time, Event Name, FieldName, FieldValue
10:00am, Event1, FieldName1, FieldValue1
10:00am, Event1, FieldName2, FieldValue2
10:00am, Event1, FieldName3, FieldValue3
10:00am, Event1, FieldName4, FieldValue4
10:10am, Event2, FieldName1, FieldValue1
10:10am, Event2, FieldName2, FieldValue2
10:10am, Event2, FieldName3, FieldValue3
10:10am, Event2, FieldName4, FieldValue4
10:20am, Event3, FieldName1, FieldValue1
10:20am, Event3, FieldName2, FieldValue2
10:20am, Event3, FieldName3, FieldValue3
10:20am, Event3, FieldName4, FieldValue4

Open in new window


I'm looking for a query against that view which will return a result set as follows:
Time, Event Name, FieldsAndValues
10:00am, Event1, FieldName1:FieldValue1 FieldName2:FieldValue2 FieldName3:FieldValue3 FieldName4:FieldValue4
10:10am, Event2, FieldName1:FieldValue1 FieldName2:FieldValue2 FieldName3:FieldValue3 FieldName4:FieldValue4
10:20am, Event3, FieldName1:FieldValue1 FieldName2:FieldValue2 FieldName3:FieldValue3 FieldName4:FieldValue4

Open in new window



I know I'm going to do a SELECT with a Group By on Time and Event.
I also know I've got to do some sort of aggregation to collapse the related FieldName/FieldValue pairs into one (1) column.

How do I go about doing that?

Thanks,
JohnB
SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jxbma

ASKER

RossTurner gets extra for going the extra mile with my example and turning me on to SQL Fiddle.