Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Thumbs up Unanswered: resultset with summary rows

    Hi,

    I have problem with query/sp which need to return one resultset with strucure like:

    summary row1
    detail row1
    detail row2
    detail row3
    summary row2
    detail row4
    detail row5
    ...

    Tables:
    T_HW (HW_ID INT, CatID INT, other cols (NVARCHARs, INTs, DATETIMEs, NTEXTs etc.)
    T_Category (CatID INT, Manufact NVARCHAR (100), other cols)

    The output I need is ( '|' means cols separator) :
    Manufact1 | No of recs from HW for this Manufact | nulls ...
    null | null | HW1 from Manufact1 details ...
    null | null | HW2 from Manufact1 details ...
    Manufact2 | No of recs from HW for this Manufact | nulls ...
    null | null | HW3 from Manufact2 details ...
    ...

    it needs to be one result recordset

    Thx in advance,
    MST78
    Last edited by MST78; 02-27-04 at 05:04.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: resultset with summary rows

    can you detail the meaning of your tables (and cols) ?

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Re: resultset with summary rows

    Originally posted by Karolyn
    can you detail the meaning of your tables (and cols) ?
    Of course

    T_HW is list of hardware units in company
    HW_ID - PK
    CatID - category (FK from T_Category)
    other cols are detail infos about this piece of HW

    T_Category is list of HW categories (with Manufacturer which needs to have HW pieces counted in summary row)
    CatID - PK
    Manufact - NVARCHAR(100) - name of HW piece manufacturer
    other cols are just detailed info of that category

    Some examples:
    T_Category:
    1 | AMD | details about processor1
    2 | AMD | details about processor2
    3 | Belinea | details about monitor1
    ...

    T_HW:
    1 | 1 | info f.ex. where we have this proc. of category processor1
    2 | 1 | info f.ex. where we have this proc. of category processor1
    3 | 1 | info f.ex. where we have this proc. of category processor1
    3 | 2 | info f.ex. where we have this proc. of category processor2
    ...

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: resultset with summary rows

    this should give you a start :

    Select HW.Name, HW.Info, null, HW.HWID, null
    From HW
    Union
    Select null, null, CAT.Detail, HW.HWID, CAT.ORDER
    From HW
    Inner Join CAT On CAT.IDCAT=HW.IDCAT
    Order By 4, 5

  5. #5
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Thumbs up Re: resultset with summary rows

    Thanks a lot

Posting Permissions

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