Hi friends,

I need to write a crosstab query in oracle like we do in Access. Here are my sample tables.

tblCat
-------
sno (primary key)
cat_id

tblDistribution
----------------
area_id
dist_id (primary key)
dist_name

tblConsumer
--------------
serviceno (primary key)
sno (references sno(tblCat))
area_id

I need the display as follows:

categories
------------
dist_id & dist_name 1 2 3 4 5 .... (cat_ids...)
---------------------------------------------------------------
id, name concatenated count(serviceno)......

ie., I want the No.of service for each category Id and for dist_id and dist_name..... The cat_id should not be duplicated.

Plz help me out of thios query as I need it badly. I've done it using crosstab query wizard in Access. here is the query

TRANSFORM Count(tb_consumer.Service_No) AS CountOfService_No
SELECT tb_distribution.Dist_Id + " - " +tb_distribution.Dist_Name AS Id_Name
FROM (tb_category INNER JOIN tb_consumer ON tb_category.SNO = tb_consumer.SNO) INNER JOIN tb_distribution ON tb_consumer.Area_Id = tb_distribution.Area_Id
GROUP BY tb_distribution.Dist_Id, tb_distribution.Dist_Name
PIVOT tb_category.Cat_Id;

It's actually like distribution wise and category wise service count list...ok!

If any one can, plz tell me how to write or convert the same to ORACLE or SQL Server.

Thanks in advance....!