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

[CRPence <CRPbottle@xxxxxxxxx>]

  Assuming a prior statement of the requirement that "I need to pick 
FieldName based on a LIKE ends with condition (like '%ield1'), not an 
equal condition..." is accurate, then again, beware the possibility of 
accidentally selecting more than one record when using the statement 
using the MAX aggregate to force reduction of the results to just the 
one row.  The LOCATE scalar is the equivalent of the LIKE predicate 
whereby the literal\constant is between the zero-to-many replacement 
character; i.e. the % [percent sign].  Thus the following two predicates 
are effectively identical:

    LOCATE('Fld1',FFIELDNAM)>0 /* or: LOCATE('ield1',FFIELDNAM)>0 */

    FFIELDNAM LIKE '%Fld1%'    /* or: FFIELDNAM LIKE '%ield1%'    */

  A simple example of a conundrum is FFIELDNAM having one row with 
'Fld11' and another [that ends] with 'Fld1'.

Regards, Chuck

On 03 Dec 2012 12:18, Richard Schoen wrote:
> OK it works.  <<SNIP>>
>
> Select FFORMID,
> 	Char(max(case when LOCATE('Fld1',FFIELDNAM)>  0
> 			  Then trim(ffieldval) end),30) as fld1 , ...
>
> Richard Schoen on Monday, December 03, 2012 10:43 AM wrote:
> > 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