matrix or crosstab query

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;

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

Thanks in advance...!The process is very simple.

You need to create a pivot table :

field1 will appear as row header
field2 will appear as column header

Select field1, field2 into pivot from mytable where ....

This gives you a table with all the data you need to work with.

Now ...

Select field1, count(field2) as totalfield2,
sum(case field2 when 'xxxx' then 1 else 0) as colhdr1,
sum(case field2 when 'yyyy' then 1 else 0) as colhdr2,
sum(case field3 when 'zzzz' then 1 else 0) as colhdr3
from pivot
group by field1
order by field1

This should produce the results much like the Access CrossTab query. You can refine it to meet your requirements. I did not fully understand what you were looking for, but hopefully this will help.

you need to recreate the pivot table every time you run!

Master-Details Insert

Master table (tlbProduct) having productID as Primary key and
which acts as reference key for table (tlbCategory).
Fields for tlbProduct are productID,productName
Fields for tlbCategort are productID,CategoryID(primary key),CategoryName,Prize
both productID,CategoryID are autoincrementing.
but when i write two inserts simultaneously as follows
insert into tlbProduct(productName)values(@.productName)
insert into tlbCategory(CategoryName,Prize) using sqldatasource
I get the error that ProductID value is null which is not provided
But (productId in both is autoincremented) and relationship is there in both tables
How to resolve this problem without adding ProductID in second Insert
?
SWati

Hello,

to create a relationship between two tables in sql, you need to match the primary key and the foreign key. So after the first insert, you would need to find out the id that was inserted by the database for the product (you can do this with SELECT SCOPE_IDENTITY()), and then you need to use this id in the second insert statement. If the keys don't match and the database is trying to enforce a relationship between the tables, you will get an error.

Good luck