Transpose from columns to rows

Database
Enthusiast

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

3 REPLIES
Junior Contributor

Re: Transpose from columns to rows

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
Enthusiast

Re: Transpose from columns to rows

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?

Junior Contributor

Re: Transpose from columns to rows

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