All Forums Database
Niesh20us 78 posts Joined 06/13
20 Mar 2015
Transpose from columns to rows

Hi Experts,
I have a situation where I have to convert rows to columns 
 
Input is :-
 
Cust_ID                         Type
12345                           FT
12345                           ET
11111                          FT
22222                          ET
33333                         FT
33333                         ET
44444                        NULL
 
Output :-
Count (FT and ET Both)             2   -------------it should give count of unique cust_id i.e. 12345 and 33333
Count(Only FT)                         1---------------- it should give count of unique cust id i.e. 1111
Count(Only ET)                         1--------------- it should give count of unique cust id i.e. 22222
Count(No value)                        1--------------- it should give count of unique cust id i.e. 44444
 
 
 

dnoeth 4628 posts Joined 11/04
22 Mar 2015

You need nested aggregrations, e.g.

select
   count(case when typestring = 'FE' then 1 end) as count_FTET
  ,count(case when typestring = 'F'  then 1 end) as count_FT
  ,count(case when typestring = 'E'  then 1 end) as count_ET
  ,count(case when typestring = ''   then 1 end) as count_NULL
from
 (
   select  -- one row per customer with a concatenated list of types
      Cust_ID
     ,max(case when Type = 'FT' then 'F' else '' end) ||
      max(case when Type = 'ET' then 'E' else '' end) as typestring
   from vt
   group by 1
 ) as dt

 

Dieter

Niesh20us 78 posts Joined 06/13
23 Mar 2015

you are awesome Dieter Thanks :)
Just wanna make sure , In the subquery you used Max function I guess it does not matter we use Max or Min? Is that right?

dnoeth 4628 posts Joined 11/04
23 Mar 2015

Of course not, '' is less than 'F' :)

Dieter

You must sign in to leave a comment.