Combining multiple rows to colums/ pivot tables to get rows into cols

[DannyBoy217]

Heya ppl,

I have a small problem, I need to combine multiple rows into one row. the rows come back like (please be advised displayed is crud data, not live!):

UID OID Keyword_List
--- --- ------------
01 101 The
02 101 truth
03 101 is
04 101 out
05 101 there
06 102 The
07 102 X-Files
08 103 small
09 103 fish
10 103 fry
11 103 fast

so what I need to do is combine them on OID (ID Carried from original) so all 101, 102 and 103 so I need to compile the rows into columns. (Don't you just love data scrubbing)


ID | Col1 | Col2 | Col3| Col4 | Col5 |
--------------------------------------------
101 | The | truth | is | out | there |
102 | The | X-Files | | | |
103 | small | fish | fry | fast | |


any ideas folks??

Thanks a million

 

[SQLSister]

MAybe this will help
Concatenating a one-to-many relationship into a single column
faq183-2146

 

[DannyBoy217]

Cheers SQLSister, though one minor problem, I can get them into a single column, In fact I think the data came from a single column, though I need to get it to parse it out so it places one word into each column (don't ask, am just keeping up a structure created by my predesessor).

so unfortunately 'Concatenating a one-to-many relationship into a single column' was a good read, though not what I needed, any more ones you have found would be greatly appreciated.

 

[SQLSister]

Ouch didn;t notice the must be separate columns part. Sorry.

AS I see it you have two problems. First how to determine how many columns to use and second putting the data in the correct one.
Example of how to get the first one:
Select Max (RecNo) from (Select COunt(*) as REcNO from Airport group by State)n

To get the second, I'd suggest changing your structure to add a column to store the record number for each OID. Then ruun a cursor to figure out which column each record would go in RecordID 1 for OOID 1 goes to column 1, recordID 2 for OID 1 goes to column 2, RecordID 1 for OID 2 goes to column 1 etc. This could also be done ina temp tablebut if you are going to do this repeatedly, I'd rather see it inthe table and updated every time you add or delete a record through triggers than running the cursors int he future.

Once you know which record will go to which column the insert statement becomes much easier. Record ID one records will go in as an insert with the column 1 fiulled in. All other records will be an update and the column will be dictated by which record it is.

Anybody got a better way to do this, I'd love to see it, but that's what I can see to do.

 

[DannyBoy217]

Cheers SQLSister,

though I'm affraid I cracked it (at about 3am last night) though you are spot on

Many thanks for investigating,
DannyBoy

 

[adventurous1]

Anyone have a solution for how to do this in the Oracle flavor of SQL or in PL/SQL?

Thanks,

Adventurous1@ureach.com