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

Richard & Eric,

That will certainly work, but a much faster solution does not require the complex logic being performed by the query engine.

So, I created the following file:

Create Table QTEMP.FLATTENME (ID, Name, Value) As
(Values
(1, 'Field1', 'I am the data'),
(1, 'Field2', 'I am the second data'),
(1, 'Field3', 'I am the third data')) With Data

Then executed this statement:

Select
ID,
Max(FLDVAL1) As RCD1ASFLD1,
Max(FLDVAL2) As RCD2ASFLD2,
Max(FLDVAL3) As RCD3ASFLD3
From
QTEMP.FLATTENME
Left Join Lateral
(Values
('Field1', VALUE, null, null),
('Field2', null, VALUE, null),
('Field3', null, null, VALUE)) As B (FLDNAM, FLDVAL1, FLDVAL2, FLDVAL3)
On
NAME = FLDNAM
Group By
ID

And received this data:

---------------------------------------------------------------------------
| id | rcd1asfld1 | rcd2asfld2 | rcd3asfld3 |
---------------------------------------------------------------------------
| 1 | I am the data | I am the second data | I am the third data |
---------------------------------------------------------------------------

This query only has to compute an aggregate max function on the columns. Obviously, there's not going to be much difference over one record, but over millions, it's huge!!!

Jim

From: EDeLong@xxxxxxxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Interesting SQL Question - Flatten Multiple Records into One Record
Date: Fri, 30 Nov 2012 23:25:40 +0000

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.