Re: SQL to return certain number of a column values concat to each other?

From: Conan <conan_at_>
Date: Mon, 25 Sep 2000 09:38:52 +0100
Message-ID: <haEz5.3376$Bw1.1836@news.indigo.ie>

Try something like this
SQL> select Id from test1
2 /

10 rows selected.

SQL> get me
  1 select a.id || ' ' || b.id || ' ' || c.id   2 from
  3 (select rownum num, id from test1
  4 ) a,
  5 (select rownum -1 num, id from test1   6 ) b,
  7 (select rownum -2 num, id from
  8 test1) c
  9 where

 10      a.num = b.num (+)
 11      and a.num = c.num (+)
 12*     and mod (a.num,3)=1

SQL> / A.ID||''||B.ID||''||C.ID

129 230 515
4000 2398 456
239 1234 10
5160

SQL> If you need to pull the values in a particular order the inline views get slightly more complicated but it can still be done. in oracle 8i and above change the inline views to

(select rownum ,id from (select id from test 1 order by id ))

in oracle7 use

(select rownum , id from (select id from test1 group by id) ) (not 100% reliable as indexes on the column by be used which bipasses the binary sort)

Hth

Conan

Scott Schaefer wrote in message <39CE6B90.DC583AC9_at_one.net>...
>Someone smarter than I would have to tell you (and me) how to do
>this with a view. OTOH, this is doable (even 'simple') with a stored
>procedure.
>
>But then, that raises question of the ORDER in which the rows are to
>be returned [which obviously affects the concatenated values]. Is
>the desired order of the rows from the underlying table fixed [i.e
>becomes part of stored procedure], or is your desire to do this with
>a view based on the desire to specify a sort order at time of SELECT ??
>
>
>adamvt_at_my-deja.com wrote:
>>
>> I wonder if this is possible with SQL? Say I have a table and if I
>> SELECT column of it I get:
>>
>> SELECT a_col FROM a_tab;
>>
>> a_col
>> -----
>> 10
>> 5160
>> 129
>> 230
>> 515
>> 4000
>> 2398
>> 456
>> 239
>> .
>> .
>> And now I wanna create a view based on this table where I'll be able to
>> get something like:
>>
>> SELECT a_conc_col FROM a_view;
>>
>> a_conc_col
>> -----------
>> 10 5160 129
>> 230 515 4000
>> 2398 456 239
>> .
>> .
>> which concats every three values together. Is this possible? if so
>> could you provide an example?
>> Thanks in advance!
>> Adam Tadj
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
Received on Mon Sep 25 2000 - 03:38:52 CDT