Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Grouping Results

    I have a syntax question but not too sure how to phrase it, so I apologize in advance if it gets to be too wordy.

    I'm querying a series of tables which have parent/child relationship. Let's say the parent table has five unique row identifiers(keys). The child table has five children per parent key.

    Is it possible to perform a query that will list this as though it were a tree view? For example, a normal select I ran turned out like this:

    PARENT DESC CHILD MISC
    1 rowone 45 junk1
    1 rowone 46 junk2
    1 rowone 55 junk3
    1 rowone 99 junk4
    2 rowone 23 jun1
    2 rowone 11 jun2
    2 rowone 34 jun3

    Is it possiblee to get this somehow:?

    PARENT DESC CHILD MISC
    1 rowone 45 junk1
    46 junk2
    55 junk3
    99 junk4
    2 rowone 23 jun1
    11 jun2
    34 jun3

    Might be a bit much to ask but any help would be appreciated. I've attached an actual screen shot of trhe data if that may be of any help.

    Thanks in advance!
    Attached Thumbnails Attached Thumbnails results.bmp  
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Isn't presentation what applications are for?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Extremely good point...that's what I said....
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Grouping Results

    Originally posted by ansonee

    Is it possiblee to get this somehow:?

    PARENT DESC CHILD MISC
    1 rowone 45 junk1
    46 junk2
    55 junk3
    99 junk4
    2 rowone 23 jun1
    11 jun2
    34 jun3

    I think SQL Cookbook contains some examples, under "Recursive queries", which can give you an idea.

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Something like this...
    Code:
    create table grouping_test
    (col1 char(1)
    ,col2 char(1)
    );
    
    insert into grouping_test
    values ('A','X')
    ,      ('A','Y')
    ,      ('A','Z')
    ,      ('B','P')
    ,      ('B','Q')
    ,      ('B','R')
    ,      ('C','A')
    ,      ('A','W')
    ,      ('C','B');
    
    select case rn
             when 1 then col1
             else ''
           end , col2
    from ( select col1, col2, row_number() over (partition by col1 order by col1, col2) rn
           from grouping_test
         ) temp_table
    ;
    Damian

Posting Permissions

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