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
LVL 1
jxbmaSoftware ConsultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
i created this example for you

http://sqlfiddle.com/#!3/a60f1/5

Create Table TEST 
(
[Time] varchar(30), 
[Event Name] varchar(30),
[FieldName] varchar(30), 
[FieldValue] varchar(30)
)


INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:00am',' Event1',' FieldName1',' FieldValue1');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:00am',' Event1',' FieldName2',' FieldValue2');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:00am',' Event1',' FieldName3',' FieldValue3');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:00am',' Event1',' FieldName4',' FieldValue4');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:10am',' Event2',' FieldName1',' FieldValue1');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:10am',' Event2',' FieldName2',' FieldValue2');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:10am',' Event2',' FieldName3',' FieldValue3');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:10am',' Event2',' FieldName4',' FieldValue4');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:20am',' Event3',' FieldName1',' FieldValue1');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:20am',' Event3',' FieldName2',' FieldValue2');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:20am',' Event3',' FieldName3',' FieldValue3');
INSERT INTO TEST(Time, [Event Name], FieldName, FieldValue) VALUES('10:20am',' Event3',' FieldName4',' FieldValue4');

Open in new window


select 
time,[Event Name],
 STUFF(
               (SELECT      ',' + SubTableUser.FieldName +':'+FieldValue
               FROM      TEST AS SubTableUser
               WHERE      SubTableUser.time = test.time
               FOR XML PATH('')), 1, 1, '') AS FIELDREF
from test
group by time,[Event Name]

Open in new window


sql
0
 
Manuel Marienne-DuchêneITMCommented:
0
 
jxbmaSoftware ConsultantAuthor Commented:
RossTurner gets extra for going the extra mile with my example and turning me on to SQL Fiddle.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.