A close look at your sample SQL shows I should have recommended LOCATE() instead.
LOCATE(FFIELDNAM, 'Field1') > 0 indicates the value "Field1" is in string FFIELDNAM.
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Monday, December 03, 2012 9:50 AM
To: Midrange Systems Technical Discussion
Subject: RE: Interesting SQL Question - Flatten Multiple Records into One Record
Richard,
I try to avoid LIKE as much as possible, so I often get creative with alternatives.
In your example, it appears you want to ignore the first character, which presumably could be upper or lower case. Easy fix is to wrap the field in UPPER() or LOWER() to monocase the string, then test for equality...
-Eric
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
Sent: Saturday, December 01, 2012 7:06 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Interesting SQL Question - Flatten Multiple Records into One Record
Jim,
Thanks for your response.
Your answer is interesting as well.
In my case I will have some unknown number of fields(value pair records) (usually around 50-100) where I may want pick off 30 or 40 of them for the ID = 1
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!!!
I may be picking out only 30-40 fields that belong to ID=1 out of possibly millions. This means my resultsets will typically only contain 50-100 fields per ID.
When you say this could be slow, doesn't the SQL engine first evaluate the ID = 1 to limit the subset of records before processing the 30-40 selected records ?
I would think that would probably remain very fast. I do have the option to put each ID resultset into a temp table before processing if needed, but am hoping that's not necessary.
Note:
One other think I forgot to mention in my case is that I need to pick FieldName based on a LIKE ends with condition (like '%ield1'), not an equal condition the way Eric showed me. I'm not sure if your example would be easily modifiable. I just changed the CASE statement to a LIKE and got what I needed. Oh, and one other thing. FieldValue is actually a 5000 byte buffer so I had to define the field lengths on the fly as well. I know my data will typically only have around 30 bytes or less of data, so shrinking the field sizes is paramount to keep the return data buffer small.
Here's a real world example of how it currently looks in testing:
select FFORMID,
char(max(Case when FFIELDNAM like '%Fld1'
then trim(ffieldval) end),30) as fld1 ,
char(max(Case when FFIELDNAM like '%Fld2'
then trim(ffieldval) end),30) as fld2
from formfld where FFORMID =
'cb3c0801-93a8-1940-ad63-0004ac10'
group by fformid
Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Email: richard@xxxxxxxxxxxxxxx
Web Site:
http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT
------------------------------
message: 4
date: Fri, 30 Nov 2012 18:56:12 -0500
from: Jim It <jim_it@xxxxxxxxxxx>
subject: 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
midrange.com
