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

Question

Hi,

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             zxq             fgw             tie
2           oaq             zxq             pie             tie 

Is something like this possible and if so, how?

Im using Oracle 9i.

Thanks,
R. Paul

Answer 1

Check out the answer here:

http://stackoverflow.com/questions/365238/advice-using-pivot-table-in-oracle

Answer 2

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.