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.developersdex.com ***

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/default.aspx?scid=kb;en-us;175574&Product=sql

Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServer/Article/ArticleID/15608/15608.html

Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955


AMB

Show quoteHide quote

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

Show quoteHide quote

Index Tuning Wizard
Update Trigger
Return Variable In Resultset!
WHILE Loop with parameters from a varchar parameter
Stored procedures, permissions, table names