Re: Best way to convert rows to column?

From: bung ho <bung_ho_at_hotmail.com>
Date: 28 Aug 2002 11:59:58 -0700
Message-ID: <567a1b1.0208281059.14f2137f_at_posting.google.com>

if you really are ok with a max of 5 children, and you have analytic functions (version 8.1.?, i forget exactly) you can do

select emp_id,

max(decode(r, 1, child_id, null)) child_1, 
max(decode(r, 2, child_id, null)) child_2, 
max(decode(r, 3, child_id, null)) child_3, 
max(decode(r, 4, child_id, null)) child_4, 
max(decode(r, 5, child_id, null)) child_5

from
(select emp_id, child_id,
 row_number() over (partition by emp_id order by emp_id) r  from children)
group by emp_id

hth

eecao_at_yahoo.com (Evan Cao) wrote in message news:<a618084.0208270438.9f6a4fd_at_posting.google.com>...
> Assume two tables EMP(EMP_ID,etc) and Childern(EMP_ID, Child_ID, etc)
> with a Master/Detail relation, meaning
> one employee can have 0 upto five n childern( say limited to 5). Now
> want to have a sql generate the output like the following, Any
> suggestions will be very appreciated.
>
> EMP TABLE DATA Child table Data
> 101 101 1001
> 102 101 1002
> ... 102 1004
> 102 1005
> 102 1006
> ...
>
> Result expected:
>
> EMP_ID Child_1 Child_2 Child_3 Child_4 Child_5
> 101 1001 1002 NULL NULL NULL
> 102 1004 1005 1006 NULL NULL
Received on Wed Aug 28 2002 - 20:59:58 CEST