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

OK it works. Thanks.

FYI The two values need to be flipped. Search string first and field name second for anyone else who finds this useful.

Select FFORMID,
Char(max(case when LOCATE('Fld1',FFIELDNAM) > 0
Then trim(ffieldval) end),30) as fld1 , ...

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: Mon, 3 Dec 2012 17:22:23 +0000
from: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
subject: RE: Interesting SQL Question - Flatten Multiple Records into
One Record

Select FFORMID,
Char(max(case when LOCATE(FFIELDNAM, 'Fld1') > 0
Then trim(ffieldval) end),30) as fld1 , ...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
Sent: Monday, December 03, 2012 10:43 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Interesting SQL Question - Flatten Multiple Records into One Record

Eric,

How would LOCATE be used in my sample SQL ?

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