Grouping Results

ansonee · #1 (**permalink**) 04-07-04, 14:06

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!

J Petruk · #2 (**permalink**) 04-07-04, 14:52

Isn't presentation what applications are for?

ansonee · #3 (**permalink**) 04-07-04, 15:03

Extremely good point...that's what I said....

n_i · #4 (**permalink**) 04-07-04, 15:37

Quote:

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.

Damian Ibbotson · #5 (**permalink**) 04-08-04, 03:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech