Need help using sql join to convert rows to columns (Oracle 9i)

I have a table, with abt 22 columns and 6-7 thousand rows in the following format

Seq_num     unique_id   name  ...   
------------------------------------
1          1           abc                  
1          1           cde      
2          1           lmn          
2          1           opq      
3          1           pqr      
4          1           stu      
1          2           oaq      
2          2           zxq      
3          2           fgw      
3          2           pie      
4          2           tie      

i'm trying to convert 4 consecutive sequences for each unique_id into a single row which looks like

unique_id   name    ...     name    ...     name    ...     name    ... 
--------------------------------------------------------------------------
1           abc             lmn             pqr             stu
2           oaq             zxq             fgw             tie

Im using partition over unique_Id and seq_num along with left joins to achieve this.

However, I need to get all the data from the table in the following format

unique_id   name    ...     name     ...    name    ...     name     ...  
---------------------------------------------------------------------------
1           abc             lmn             pqr             stu
1           cde             opq             pqr             stu
2           oaq             zxq             fgw             tie
2           oaq             zxq             pie             tie

i.e. I need to display unique seq_num in another row (for a given unique_id) and if any seq_num is missing, use the common seq_num (from same unique_id) to fill in the blank..

for e.g.

Since unique_id 2 has two seq_num 3 (fgw,pie), unique_id 2 will have two rows that look like

2           oaq             z开发者_运维技巧xq             fgw             tie
2           oaq             zxq             pie             tie 

Is something like this possible and if so, how?

Im using Oracle 9i.

Thanks,

R. Paul

So, you need to fill in the missing gaps. The following piece of code doesn't work as intended. Thinking...

The following piece of code is a WIP. It is a first stab at generating a complete set of SEQ_NUM, UNIQUE_ID and NAME columns, but I haven't had the chance to test it yet.

select seq_num
       , unique_id
       , case 
            when name is not null then name 
            else lag (name, 1) 
               over ( partition by unique_id, seq_num 
                      order by nvl2(name, 1, 0)
                    ) end as name
from
    (    
        select t.seq_num
               , y.unique_id
               , y.name
        from ( select 1 as seq_num from dual
               union all
               select 2 as seq_num from dual
               union all
               select 3 as seq_num from dual
               union all
               select 4 as seq_num from dual ) t
        left outer join
             your_table y 
                 on ( t.seq_num = y.seq_num )   
    )

NB - I could have chosen to use the CONNECT BY trick to generate the numbers but that was a bit flaky in 9i (and was also undocumented).

Not an answer, but a request for clarification. In your example:

1           abc             lmn             pqr             stu
1           cde             opq             pqr             stu

You pair up "abc" with "lmn" in the first row, and "cde" with "opq" in the second row. Is the following set of rows also a valid "solution":

1           abc             opq             pqr             stu
1           cde             lmn             pqr             stu

Where "abc" is paired with "opq" in the first row and "cde" is paired with "lmn" in the second?

If so, I'm not sure how you can tell that you have a valid answer set, as there doesn't appear to be anything in the data that would help you distinguish the two.