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

    Unanswered: Matrix / 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....!

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    only MS Access has very nice crosstab SQL function....

    if you use big brothers, you need to go around.... you can use CASE or ORACLE has also DECODE function...


    so for example

    SELECT MyYear, SUM(CASE WHEN MyYear = 1999 then MyPrice else 0 end) as Column1999 FROM myTable
    GROUP BY MyYear


    SELECT MyYear, SUM(DECODE(MyYear,1999,MyPrice)) as Column1999 FROM myTable
    GROUP BY MyYear




    jiri

Posting Permissions

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