help on forming a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help on forming a query

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2011-01-31 : 01:29:17

I've the following data

ProductMaster
P1
P2
P3

CategoryMaster
1 P1 c1
2 P1 c2
3 P2 c1
4 P2 c2
5 P3 c1
6 P3 c2

TicketMaster
ID CategoryMaterID Status
1 1 Open
2 2 Closed
3 3 Pending

I want an output that gives me productwise, categorywise, count of statuses
In the example I mentioned above, the output will be
Product 1
Category 1
Open Closed Pending
1 0 0
Category 2
Open Closed Pending
1 0 0

and so on......

Any answers will be highly appreciated

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-31 : 07:04:46

Hi,
As per my understand i have written this.If my assumption is wrong can you clearly explain the scenario. i have question that what is categorymasterid in Ticketmaster table?

see this and clarify?

create table #ProductMaster(pid varchar(2))
insert into #ProductMaster
select 'P1' union all
select 'P2' union all
select 'P3'

create table #CategoryMaster(id int,pid varchar(2),cid varchar(2))
insert into #CategoryMaster
select 1,'P1','c1' union all
select 2,'P1','c2' union all
select 3,'P2','c1' union all
select 4,'P2','c2' union all
select 5,'P3','c1' union all
select 6,'P3','c2'

create table #TicketMaster(ID int,CategoryMaterID varchar(2),Status varchar(10))
insert into #TicketMaster
select 1,'c1','Open' union all
select 2,'c2','Closed' union all
select 3,'c3','Pending'

select * from #TicketMaster

select pid,cid as category,(case when status='open' then 1 else 0 end )as [open],
(case when status='closed' then 1 else 0 end )as closed, (case when status='pending' then 1 else 0 end )as pending
from #CategoryMaster c
join #TicketMaster t on c.cid=t.CategoryMaterID

sathish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources