Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002

    Thumbs up Unanswered: 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.

    sno (primary key)

    dist_id (primary key)

    serviceno (primary key)
    sno (references sno(tblCat))

    I need the display as follows:

    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....!

  2. #2
    Join Date
    Jul 2003
    Sydney Australia
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts