Concating rows in SQL

Question

I have a table named Dummy as shown below:

No.   Name
1     ABC
2     NMD
2     SDSDS
1     23ererer

Now i wanted to concat all Name column with a given number

For ex. say No. is 1 i want ABC23ererer as my output

This is to be done in ORACLE(SQL) without using PL-SQL.

How can this be done?

do you want to concat all names in the name column with the same number? — kevinm, Jul 04 '13 at 05:36
Maybe http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row can give some hints. — OcuS, Jul 04 '13 at 05:36

score 1 · Accepted Answer · answered Jul 04 '13 at 05:37

1

this might help...

select NO,
  listagg(NAME, ',') within group (order by NAME) as name
from TableName
group by NO

Or else check this

answered Jul 04 '13 at 05:37

Gopesh Sharma

6,730
4
25
35

Dinup Kandel · Answer 2 · 2013-07-04T05:51:09.893

LISTAGG is not supported in oracle10g. If you have 10g i think the following query will help you.

select No, rtrim(Name,',') Name
       from ( select No
      , Name, rn
         from yourtable
               model
                      partition by (No)
                        dimension by (row_number() over
                                     (partition by No order by Name) rn
                                    )
                       measures     (cast(Name as varchar2(40)) Name)
                       rules
                       ( Name[any] order by rn desc = Name[cv()]||''||Name[cv()+1]
                       )
              )
        where rn = 1
        order by NO

see for your demo in sql fiddle

you have ask's tom `stragg` since 9i [here](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15637744429336) — haki, Jul 04 '13 at 06:13

score 0 · Answer 3 · answered Jul 04 '13 at 05:40

0

SELECT "No.", LISTAGG(Name, ',') WITHIN GROUP (ORDER BY "No.") AS Name
FROM TableName
GROUP BY "No.";

answered Jul 04 '13 at 05:40

Devart

119,203
23
166
186

score -4 · Answer 4 · answered Jul 04 '13 at 05:38

-4

try this :

SELECT Number+' '+
Name FROM table_name

answered Jul 04 '13 at 05:38

Ahmed

452
3
7

Did you look at the example he gave? – Barmar Jul 04 '13 at 05:38
That's not valid SQL (neither in Oracle nor standard SQL) – Jul 04 '13 at 06:35

Concating rows in SQL

4 Answers4