CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mohamed
Mohamed

Reputation: 567

How to merge many rows into one oracle sql

I have a table a such that

Select * From a where person id =1; returns

person_id  colA colB

1           AA   BB

1           CC   DD

1           EE   FF

Now what i need is 

person_id colA colB colA_row_2, colB_Row_2  ColA_row_3 ColB_ROW_3

1          AA   BB  CC           DD           EE         FF

I will always get nine rows per person. So i can just create the columns as non dynamic but cant seem how to do it. This needs to be in pure sql no stored procedures. Thanks in advance

Upvotes: 1

Views: 173

Answers (3)

Mohamed
Mohamed

Reputation: 567

I ended up using

select MAX(DECODE(ROWNUM, 1, AA, NULL)) AA,.... MAX(DECODE(ROWNUM, 1, FF, NULL)) COLB_ROW_3 FROM (SELECT .....);

Upvotes: 0

Rock 'em
Rock 'em

Reputation: 187

You can use decode as an alternative.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238

Upvotes: 1

StanislavL
StanislavL

Reputation: 57421

You need PIVOT for the query See the examples http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html and http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

You have to define how rows are converted into columns e.g. 

pivot 
(
   count(colA )
   for colA in ('AA' as "AA",'CC' "CC"...)
)

Upvotes: 2

Related Questions