Re: SQL grouping

[Tony Carolla <carolla@xxxxxxxxx>]

Hi Lim

<snip>
> SELECT Phone#, PhoneExt#, (%trim(featsString) concat feature)  as featsString,
</snip>

You can't use recursion in SQL... What I was talking about would go
something like this:

Select  

Phone, 
PhoneExt, 

Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),1,3) As Feature1, 

Case When 
Len(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
) > 3, 
Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),4,3), ' ' End As Feature2, 

Case When 
Len(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
) > 6, 
Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),7,3), ' ' End As Feature3, 
etc.

>From lib/file

You would need to include one of the substring clauses, incrementing
the name FeatureX, the length check parameter, and the starting
position of the substring, for each feature a customer might have (ex.
if no customer has more than 5 different features, create five
clauses).  And within each clause, you would have to include a '||"
and a case statement for each possible feature code that is available.

I must warn you, your brain will hurt afterwards, and you will need a
safe ride home from work tonight ;-)


-- 
"Enter any 11-digit prime number to continue..."