query

dat temp
ani 2
fruit 3
ani 1
clothes 4
ani 3
i want to make below that from 1 table(name:tbl) upper
aniCnt clothesCnt fruitCnt
3 1 1
how make query statement?
*** Sent via Developersdex http://www.examnotes.net ***Try using a "case" expression.
select
sum(case when dat = 'ani' then 1 else 0 end) as aniCnt,
sum(case when dat = 'fruit' then 1 else 0 end) as fruitCnt,
sum(case when dat = 'clothes' then 1 else 0 end) as clothesCnt
from
tbl
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"tom taol" wrote:

>
> dat temp
> ani 2
> fruit 3
> ani 1
> clothes 4
> ani 3
> i want to make below that from 1 table(name:tbl) upper
> aniCnt clothesCnt fruitCnt
> 3 1 1
> how make query statement?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Try code below
create table #temp (dat varchar(10), [temp] int)
declare @.sql varchar(8000)
insert into #temp
select 'ani', 2 union all
select 'fruit', 3 union all
select 'ani', 1 union all
select 'clothes', 4 union all
select 'ani', 3
select @.sql = coalesce(@.sql + ', ', 'select ') + convert(varchar,
count([temp])) + ' [' + dat + 'Cnt]'
from #temp
group by
dat
exec (@.sql)
drop table #temp
Rakesh
"tom taol" wrote:

>
> dat temp
> ani 2
> fruit 3
> ani 1
> clothes 4
> ani 3
> i want to make below that from 1 table(name:tbl) upper
> aniCnt clothesCnt fruitCnt
> 3 1 1
> how make query statement?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>