× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.