Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Andy.

Asked: March 18, 2003 - 11:13 am UTC

Last updated: June 20, 2003 - 5:29 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom

How can I - in an SQL query - combine multiple rows into a one row, when I know the number of rows I want to combine (3 in my example), but don't want to test the values of each row, but just take the entries as they come. e.g.

How can I turn:

Col1 Col2
------- -----
1 a
2 b
3 c
4 d
1 e
2 f
3 g
1 h


into:

Col1 Col2
------- ------------------
1 a e h
2 b f -
3 c g -
4 d - -

i.e. I'm looking for upto 3 rows in Col2 for each value in Col1. If a row (first, second or third) doesn't exist, then I just want a null value.

Thanks in advance,

Andy

and Tom said...

ops$tkyte@ORA920> select col1, c21 || ' ' || c22 || ' ' || c23
2 from (
3 select col1, max(decode( rn, 1, col2 )) c21,
4 max( decode( rn, 2, col2 )) c22, max(decode( rn, 3, col2 )) c23
5 from (
6 select col1, col2,
7 row_number() over ( partition by col1 order by col2 ) rn
8 from t
9 )
10 where rn <= 3
11 group by col1
12 )
13 /

COL1 C21||
---------- -----
1 a e h
2 b f
3 c g
4 d


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andy, March 19, 2003 - 3:19 am UTC

Wow - Analytics do indeed rock, don't they!! Thanks for your helpful and quick answer.

Varition on this theme

Andy, June 17, 2003 - 7:56 am UTC

Tom, as a variation on this theme, how would I show column values (for, say, a set of address elements) in such a way as to "squeeze" out blank entries, so that non-empty entries are "shunted" to the left to accomodate empty entries.

e.g. I'm trying to select address elements from a table in such a way as to avoid "gaps". For instance, say my address elements (for a given row) are:

addr1 addr2 addr3 city country
----- ----- ----- ---- -------
A B NULL C D


in some cases, not all of the "addr" parts are needed (col. addr3 in this case), so I want to squeeze out the blanks, and return it all as one row like this:

element1 element2 element3 element4
-------- -------- -------- --------
A B C D

What's the best way to do this in one SQL statement?

Many thanks for your great site.

Andy

Tom Kyte
June 17, 2003 - 12:26 pm UTC

I don't see an "easy" way -- the problem is that the column you want next is dependent on previous columns and -- yuck. It gets "hard"


I guess I would use concatenation and return a SINGLE address field ( a string )

Multiple rows in a single column

Jean-Pierre, June 20, 2003 - 10:24 am UTC

Hi

I have a table with 1 column and n rows (I don(t know the number of rows -- where COL1 like '%...%' --) :
COL1
----
0001
0002
0155
0457
7988
....
....

I'd like to write a query which produce this :

COL1
----------------------------------------------
0001,0002,0155,0457,7988,......

Is there any way to obtain this ?

Thanks



Tom Kyte
June 20, 2003 - 5:29 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402 <code>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.