September 13, 2009 at 10:39 pm
i have a tables like :
this table have the columns:
-----------------------------------
prioriy table
pid name
---------------
1 critical
2 major
3 minor
status table
----------------
1 new
2 open
3 closed
4 draft
product table
-----------------
id(auto_increment) title priority status
-------------------------------------------------------------
100 a 1 1
101 a 2 1
102 a 3 1
104 a 1 2
105 a 2 2
104 a 3 2
105 a 1 3
106 a 2 3
107 a 3 3
108 a 1 4
109 a 2 4
110 a 3 4
111 a 3 4
i need this type of out put :
----------------------------------
id title pirority new open closed draft
----------------------------------------------------------------------------
1 a 1 1 1 1 1 -----
1 a 2 1 1 1 1 ---
1 a 3 1 1 1 2 ---- these are
count of stauses
... Please help me on this ...................
Thanks
Dastagiri.D
September 13, 2009 at 11:45 pm
create table #product
(
Title varchar(2),
priid int,
status int
)
insert into #product
select 'A',1,1
union all
select 'A',2,1
union all
select 'A',3,1
union all
select 'A',1,2
union all
select 'A',2,2
union all
select 'A',3,2
union all
select 'A',1,3
union all
select 'A',2,3
union all
select 'A',3,3
union all
select 'A',1,4
union all
select 'A',2,4
union all
select 'A',3,4
union all
select 'A',3,4
select Title,priid,count(A)New,count(AA)[Open],count(AAA)Closed,count(AAAA)Draft
from (
select Title,priid,
(case when status = 1 then (status) else null end) A,
(case when status = 2 then (status) else null end) AA,
(case when status = 3 then (status) else null end) AAA,
(case when status = 4 then (status) else null end) AAAA from #product
) as X
group by Title,priid
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply