Pivot / unpivot in SQL

Question

Hi,

I have a view in SQL that I have generated by analysing the values in tables so that field either contain the value 'N', 'D' or 'V'. I can work out the totals by column but not by row... Is this possible?

Example:

Data

No, Col_1, Col_2, Col_3

 1,     N,     N,     N

 2,     N,     D,     D

 3,     N,     V,     D

 4,     V,     V,     V

How do I summise that Row 3 has 1N, 1V and 3ds whilst Row 4 has 4Vs?

Bet is quite simple but sadly so am I!

Many thanks in advance, Peter

Answer 1

 select case when col_1 = 'N' then 1 else 0 end as n_count from tablename;

Generalizing that:

 select 
   case when col_1 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end as n_count,
   case when col_1 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end as v_count,
   ....
  from tablename;

Answer 2

How about?

select no,
sum(case when val = 'N' then 1 else 0 end) ncnt,
sum(case when val = 'V' then 1 else 0 end) vcnt,
sum(case when val = 'D' then 1 else 0 end) dcnt from
(select no, col_1 val from t union all 
 select no, col_2 from t union all
 select no, col_3 from t)
group by no
order by no