sql query to concat 2 rows of the same column

Question

I have the following table Tble :

Name  |  Level
--------------
n1    |  L1
n1    |  L2
n2    |  L1
n2    |  L3
n3    |  L1
n4    |  L3

How can I write a SQL query to output like that ?

Name  |  Level  
----------------
n1    |  L1_L2
n2    |  L1_L3
n3    |  L1
n4    |  L3

Thank you

What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `RDBMS` stands for *Relational Database Management System*. `RDBMS is the basis for SQL`, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo, Mar 15 '13 at 01:53
hsqldb + oracle . Is there a way that would be independent of the rdbms? — Fab, Mar 15 '13 at 01:57

score 1 · Answer 1 · answered Mar 15 '13 at 01:56

1

Assuming MySQL:

SELECT Name, GROUP_CONCAT(Level SEPARATOR "_") Level
FROM tbl
GROUP BY Name

Result

| NAME | LEVEL |
----------------
|   n1 | L1_L2 |
|   n2 | L1_L3 |
|   n3 |    L1 |
|   n4 |    L3 |

See the demo

answered Mar 15 '13 at 01:56

Kermit

33,827
13
85
121

Actually HSQLDB and ORACLE – Fab Mar 15 '13 at 02:04
HSQLDB supports this if you use single quotes around the separator. – fredt Mar 15 '13 at 02:43
@fredt Not according to [this question](http://stackoverflow.com/questions/11660432/simulate-group-concat-function-in-hsqldb-versions-2) – Kermit Mar 15 '13 at 03:40
@Aarloma - that question relates to an old version of HSQLDB in OpenOffice, not the recent versions which are used by developers. – fredt Mar 15 '13 at 11:03

score 1 · Answer 2 · answered Mar 15 '13 at 02:20

so if its always 2 rows as your question title suggests, simply do:

SQL> select name,
  2         case
  3           when min(lvl)=max(lvl) then min(lvl)
  4           else min(lvl)||'_'||max(lvl)
  5         end lvl
  6    from data group by name
  7   order by name;

NA LVL
-- -----
n1 L1_L2
n2 L1_L3
n3 L1
n4 L3

in 11g you could use listagg if its more than 2 rows per name:

SQL> select name,
  2         listagg(lvl, '_') within group (order by lvl) lvl
  3    from data
  4   group by name
  5   order by name;

NA LVL
-- --------------------
n1 L1_L2
n2 L1_L3
n3 L1
n4 L3

or in 10g:

SQL> select name,
  2         replace(wm_concat(lvl), ',', '_') lvl
  3    from data
  4   group by name
  5   order by name;

NA LVL
-- --------------------
n1 L1_L2
n2 L1_L3
n3 L1
n4 L3

score 0 · Answer 3 · answered Mar 15 '13 at 02:44

0

Here's Oracle 11g R2 solution: using LISTAGG:

SELECT "Name", LISTAGG("Level", ',') WITHIN GROUP (ORDER BY "Level") "Levels"
FROM Table1 
GROUP BY "Name"
ORDER BY "Name";

SQL Fiddle

answered Mar 15 '13 at 02:44

PM 77-1

12,933
21
68
111

score 0 · Answer 4 · answered Mar 15 '13 at 11:14

HSQLDB supports the same syntax as MySQL, but it uses the SQL Standard single quotes for string constants.

SELECT "Name", GROUP_CONCAT("Level" ORDER BY "Level" SEPARATOR '_') "Level"
FROM "Tbl"
GROUP BY "Name"
ORDER BY "Name"

This example assumes you have quoted, mixed-case table and column names.

http://www.hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_aggregate_funcs

sql query to concat 2 rows of the same column

4 Answers4