MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

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



fixed

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







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact