RE: Interesting SQL Question - Flatten Multiple Records into One Record

How's this?

Select ID, max(Case when name='Field1' then value end) as Field1,
max(Case when name='Field2' then value end) as Field2,
max(Case when name='Field3' then value end) as Field3
FROM DB
GROUP BY ID


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
Sent: Friday, November 30, 2012 5:06 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Interesting SQL Question - Flatten Multiple Records into One Record

I have an interesting SQL technical question.

Here's my scenario:

Have a DB table with value pairs like this:

ID Name Value
1 Field1 I am the data
1 Field2 I am the second data
1 Field3 I am the third data

Is there an easy way in SQL to flatten this into a single record that looks like this with individual names for each field.

ID Field1 Field2 Field3
1 I am the data I am the second data I am the third data

Any thoughts would be appreciated.

Thanks in advance

Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Email: richard@xxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxx>
Web Site: http://www.rjssoftware.com<http://www.rjssoftware.com/>
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT