Re: SQL grouping

[Tony Carolla <carolla@xxxxxxxxx>]

Okay, it was pecking at the back of my brain.. The solution:

Select  Feat1.Phone#, Feat1.PhoneExt#, 
                Substring(
                (Case when Feat1.Feature='ABC' then 'ABC' Else '' End || 
                Case when Feat2.Feature='DEF' then 'DEF' Else '' End ||
                Case when Feat3.Feature='XXX' then 'XXX' Else '' End),1,3
                As Feature1, 

                Case When Len(  
                (Case when Feat1.Feature='ABC' then 'ABC' Else '' End || 
                Case when Feat2.Feature='DEF' then 'DEF' Else '' End ||
                Case when Feat3.Feature='XXX' then 'XXX' Else '' End))>3,
                Substring(
                (Case when Feat1.Feature='ABC' then 'ABC' Else '' End || 
                Case when Feat2.Feature='DEF' then 'DEF' Else '' End ||
                Case when Feat3.Feature='XXX' then 'XXX' Else '' End),4,3
                ,'' As Feature2, 

                Case When Len(  
                (Case when Feat1.Feature='ABC' then 'ABC' Else '' End || 
                Case when Feat2.Feature='DEF' then 'DEF' Else '' End ||
                Case when Feat3.Feature='XXX' then 'XXX' Else '' End))>6,
                Substring(
                (Case when Feat1.Feature='ABC' then 'ABC' Else '' End || 
                Case when Feat2.Feature='DEF' then 'DEF' Else '' End ||
                Case when Feat3.Feature='XXX' then 'XXX' Else '' End),7,3
                ,'' As Feature3

From            (Select *
                 From   lib.file
                 Where  Feature='ABC'
                ) Feat1

Inner Join      (Select *
                 From   lib.file
                 Where  Feature='DEF'
                ) Feat2 On
                Feat1.Phone#=Feat2.Phone# And
                Feat1.PhoneExt#=Feat2.PhoneExt#

Inner Join      (Select *
                 From   lib.file
                 Where  Feature='XXX'
                ) Feat3 On
                Feat1.Phone#=Feat3.Phone# And
                Feat1.PhoneExt#=Feat3.PhoneExt#;

You need one subquery/SELECT element for each possible feature code. 
Take aspirin ;-)

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