SELECT
CNTA=SUM(CASE COL1 WHEN 'A' THEN 1 ELSE 0 END),
CNTB=SUM(CASE COL1 WHEN 'B' THEN 1 ELSE 0 END),
CNTC=SUM(CASE COL1 WHEN 'C' THEN 1 ELSE 0 END)
FROM
TAB
CNTA=SUM(CASE COL1 WHEN 'A' THEN 1 ELSE 0 END),
CNTB=SUM(CASE COL1 WHEN 'B' THEN 1 ELSE 0 END),
CNTC=SUM(CASE COL1 WHEN 'C' THEN 1 ELSE 0 END)
FROM
TAB
insert into @tab values('A')
insert into @tab values('A')
insert into @tab values('A')
insert into @tab values('B')
insert into @tab values('B')
insert into @tab values('C')
insert into @tab values('C')
insert into @tab values('C')Select
(Select Count(1) From @tab Where Col1 = 'A') CNTA,
(Select Count(1) From @tab Where Col1 = 'B') CNTB,
(Select Count(1) From @tab Where Col1 = 'C') CNTC
CNTB=(select count(1) from Tab where COL1='B'),
CNTC=(select count(1) from Tab where COL1='C')
set @s = ''
select @s = ','+'[cnt'+COL1+'] = sum(case col1 when '+col1+ ' then 1 else 0 end)'
from (select distinct col1 from table order by col1)
set @s = stuff(@s,1,1,'')
exec('select distinct '+ @s + ' from table ')
insert into tab values('A')
insert into tab values('A')
insert into tab values('A')
insert into tab values('B')
insert into tab values('B')
insert into tab values('C')
insert into tab values('C')
insert into tab values('C')
go
select distinct col1 from tab
declare @s varchar(8000)
set @s = ''
select @s = @s+','+'[cnt'+COL1+'] = sum(case col1 when '''+col1+ ''' then 1 else 0 end)'
from (select distinct col1 from tab ) a
set @s = stuff(@s,1,1,'')exec('select distinct '+ @s + ' from tab ')drop table tab
/*cntA cntB cntC
----------- ----------- -----------
3 2 3*/