I need SQL query for this data situation in Oracle

Question

I have the following data:

column1        column2
   1             aaa
   1             bbb
   2             ccc
   2             ddd
   2             eee

The expected output should be one row per column1 type which contains all of the column2 data, as below:

column1,   column2
   1,      aaa|bbb
   2,      ccc|ddd|eee

How can I get this output ?

I am Using below Query to get this data.

select sub_id,listagg(pack_name, '|')  within group(order by sub_id) package_name 
from Recon_Droppacks_Migration group by sub_id;

col1:sub_id
col2:pack_name
table:Recon_Droppacks_Migration

And post some working query, does not matter is incorrect, is a starting point — Yaroslav, Oct 15 '12 at 12:44
`select 1 as column1, 'aaa|bbb' as column2 union all select 2 as column1, 'ccc|ddd|eee' as column2` — Adam Robinson, Oct 15 '12 at 12:46
@MahmoudGamal: Yes, that's fairly obvious, but questions without at least an attempt at solving the problem don't usually get serious responses. — Adam Robinson, Oct 15 '12 at 12:49
or another SQL Server answer: http://stackoverflow.com/questions/4815260/string-join-in-sql — paul, Oct 15 '12 at 12:49

Taryn · Answer 1 · 2012-10-15T14:11:44.063

You did not specify the RDBMS you are using. So here is how you will do this in a variety of DBMS.

MySQL, you will use GROUP_CONCAT():

SELECT column1, GROUP_CONCAT(column2 SEPARATOR ' | ') column2
FROM table1
GROUP BY column1

See SQL Fiddle with Demo

In SQL Server, you will use FOR XML PATH:

select 
  t1.col1,
  STUFF((SELECT distinct ' | ' + col2
              from table1 t2
              where t1.col1 = t2.col1
            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)') 
        ,1,3,' ') col2
from table1 t1
group by t1.col1

See SQL Fiddle with Demo

In Oracle, you can use LISTAGG():

select "col1", 
  listagg("col2", ' | ') within group(order by "col1") col2
from table1
group by "col1"

See SQL Fiddle with Demo

In Postgresql you can use string_agg():

SELECT column1, string_agg(column2, ' | ') col2
FROM Table1
GROUP BY column1;

See SQL Fiddle with Demo

Edit, based on your comment, you will want to use:

select sub_id, listagg(pack_name, ' | ') within group(order by sub_id) package_name 
from Recon_Droppacks_Migration 
group by sub_id;

The Above Query got an error as "ORA-00923: FROM keyword not found where expected". Please help in getting this. — Niranjan, Oct 16 '12 at 07:05
@Niranjan I am not sure why you would be getting this error, see this demo with both my original sample and your version working -- http://sqlfiddle.com/#!4/a2289/2 Is this the exact query you are running? — Taryn, Oct 16 '12 at 09:43

Himanshu · Answer 2 · 2012-10-15T12:55:46.917

1

With MySQL: Use GROUP_CONCAT

SELECT column1, GROUP_CONCAT(column2 SEPARATOR ' | ') as column2
    FROM Table1 
    GROUP BY column1

See SQLFiddle Demo

With SQL Server:

SELECT distinct column1, column2 = 
    STUFF((SELECT ' | ' + column2
           FROM Table1 b 
           WHERE b.column1 = a.column1 
          FOR XML PATH('')), 1, 2, '')
FROM Table1 a
GROUP BY column1;

See SQLFiddle Demo

edited Oct 15 '12 at 12:55

answered Oct 15 '12 at 12:49

Himanshu

31,810
31
111
133

score 1 · Answer 3 · answered Oct 15 '12 at 12:53

Use a recursive query:

SELECT *, 
       Row_number() 
         OVER ( 
           PARTITION BY COLUMN1 
           ORDER BY COLUMN1 ) rn 
INTO   #TEMP1 
FROM   TABLE1; 

WITH RECUR 
     AS (SELECT *, 
                Cast(COLUMN2 AS VARCHAR(MAX)) AS full_text 
         FROM   #TEMP1 
         WHERE  RN = 1 
         UNION ALL 
         SELECT t1.*, 
                Cast(t2.FULL_TEXT + ' | ' + t1.COLUMN2 AS VARCHAR(MAX)) 
         FROM   #TEMP1 t1 
                INNER JOIN RECUR t2 
                        ON t1.COLUMN1 = t2.COLUMN1 
                           AND t1.RN = t2.RN + 1) 
SELECT t1.COLUMN1, 
       t1.FULL_TEXT 
FROM   RECUR t1 
       INNER JOIN (SELECT Max(RN) rn, 
                          COLUMN1 
                   FROM   RECUR 
                   GROUP  BY COLUMN1) t2 
               ON t1.COLUMN1 = t2.COLUMN1 
                  AND t1.RN = t2.RN

See full example here

score 0 · Answer 4 · edited May 23 '17 at 11:43

This solution Does T-SQL have an aggregate function to concatenate strings? works fine, because it doesn't fail on xml characters.

declare @Temp_Table table (column1 bigint, column2 nvarchar(max))

insert into @Temp_Table (column1, column2)
select 1, 'aaa' union all
select 1, 'bbb' union all
select 2, 'ccc' union all
select 2, 'ddd' union all
select 2, 'eee'

select distinct
    t1.column1,
    stuff(
        (
            select
                ' | ' + t2.column2
            from @Temp_Table as t2
            where t2.column1 = t1.column1
            order by t2.column2 for xml path(''), type
        ).value('.', 'nvarchar(max)')
    , 1, 3, '')
from @Temp_Table as t1

You can also write a cursor and use variable trick

score -1 · Answer 5 · answered Oct 15 '12 at 13:06

please try query given below

SELECT concat(CONVERT(column1,char(8))," , ") AS id,group_concat(CONVERT(column2, CHAR(20)) separator '|') AS result FROM `tablename` WHERE 1 group by column1

Here concat is using to append , with field column1 and CONVERT is using to convert it into as string because it works on string better. and GROUP_CONCAT is using to append all result for particular column1 with given saperator.

thanks

I need SQL query for this data situation in Oracle

5 Answers5

See SQLFiddle Demo

See SQLFiddle Demo