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

    Unanswered: Matrix or Crosstab queries

    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Matrix or Crosstab queries

    There is no way to do this in pure SQL - UNLESS you are happy to hardcode the category values into the query. If that's OK, then you can do it like this:

    SELECT dist_id, dist_name,
    SUM( DECODE( cat_id, '1', 1, 0 ) ) as "1",
    SUM( DECODE( cat_id, '2', 1, 0 ) ) as "2",
    ...
    SUM( DECODE( cat_id, '19', 1, 0 ) ) as "19"
    FROM ...
    GROUP BY dist_id, dist_name;

    However, if you don't want a static list of columns like that, then you will have to use dynamic SQL in PL/SQL to construct a SELECT statement similar to the above based on the current cat_id values in tblCat, or use a reporting tool - e.g. Oracle Reports can do crosstab queries.

  3. #3
    Join Date
    Dec 2002
    Posts
    5

    ThanQ

    Hi

    Actually it's like I need Distribution wise and Category wise service count list. If I need to hard code categories, can u plz tell me how can I do it in PL/SQL.Can I make it a stored procedure and call from a VB program...this is becoz I am programming in vb.... I'll be pleased if u can give me a detailed reply to rkvmca@rediffmail.com with some more help info like some valuble links and all...ThANQ!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ThanQ

    If you are programming in VB and creating SQL statements as string variables which you pass to Oracle via ODBC, then you can do most of the work in VB. I'll use pseudoVB, as my VB is poor (have used VBScript occasionally, but forget the correct syntax now) - but I hope you'll get the idea:

    ' Start the crosstab query statement
    strCrosstabSQL = "SELECT dist_id, dist_name"

    ' get all the cat_ids and add a column for each to the crosstab query
    rec = Execute( "SELECT cat_id FROM tblCat ORDER BY cat_id" )
    WHILE not EOF DO
    strCrosstabSQL = strCrosstabSQL & ", SUM( DECODE( cat_id, '" & rec.cat_id & "', 1, 0) as c" & rec.cat_id
    LOOP

    ' Finish the crosstab query statement
    strCrosstabSQL = strCrosstabSQL & " FROM ... WHERE ..."

    ' Run the crosstab query
    rec2 = Execute( strCrosstabSQL )

    ...etc.

    As an alternative, you could perform the above logic in a PL/SQL stored procedure that returns a REF CURSOR for the result set - but I don't know if that's feasible from VB or not. The PL/SQL version would construct a string in the same way as above, then:

    OPEN p_cursor FOR strCrosstabSQL;

    ... where p_cursor is a parameter that passes the result set back to the calling program.

Posting Permissions

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