Help for Count Query

[DDE]

i have two tables,
Table 1
---------------------------
Col1 Col2
--------------------------
A1 D
A2 D
A1 W
A3 D
A4 W
A2 W
A5 D
A1 D
A2 W
A3 W

Table 2
------------------------
Col1 Col2
-------------------------
A1 Name1
A2 Name2
A3 Name3
A4 Name4
A5 Name5

I want to get the count of all rows with D and W values in the following format..
-------------------------------------------------------
Name Count_D Count_W
-------------------------------------------------------
Name1 2 1
Name2 1 2
Name3 1 1
Name4 0 1
Name5 1 0
----------------------------------------------------------------
Is it possible to achieve this through query..Please help to write query for this

[kabilahan]

hi try this...

select * from(
select col1=t2.col2,col2=t1.col2
from table_1 t1
join table_2 t2
on t1.col1 = t2.col1
) y
pivot(count(col2) for [col2] in (d,w))as te

output is:
-------------
col1 D W
name1 2 1
name2 1 2
name3 1 1
name4 0 1
name5 1 0

[DDE]

hi try this...

select * from(
select col1=t2.col2,col2=t1.col2
from table_1 t1
join table_2 t2
on t1.col1 = t2.col1
) y
pivot(count(col2) for [col2] in (d,w))as te

output is:
-------------
col1 D W
name1 2 1
name2 1 2
name3 1 1
name4 0 1
name5 1 0

Hello,
There is something missing in this query..it gave me an error saying "Incorrect syntax near 'pivot'."..Please will u check the query once more..

[frozenmist]

Hi,
This is an alternate way that should work.

 
select a.col1 "NAME",isnull(b.cnt ,0) "Count_D",isnull( c.cnt ,0) "Count_W" from table_2 a 
left outer join 
(select col1,col2,count(*) cnt from table_1 where col2='D' group by col1,col2) b
on a.col2=b.col1 left outer join
(select col1,col2,count(*) cnt from table_1 where col2='W' group by col1,col2)c 
on a.col2=c.col1
 
 

Hope you problem is solved
Cheers

[kabilahan]

Its working ...i changed the column name.............
try this..

select * from(
select c1=t2.col2,c2=t1.col2
from table_1 t1
join table_2 t2
on t1.col1 = t2.col1
) y
pivot(count(c2) for [c2] in (d,w))as te

[kabilahan]

Its working ...i changed the column name.............
try this..

select * from(
select x1=t2.col2,x2=t1.col2
from table_1 t1
join table_2 t2
on t1.col1 = t2.col1
) y
pivot(count(x2) for [x2] in (d,w))as te