Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

How to Concatenate Column Values from Different Rows

Sometimes you want to concatenate all column values from differenct rows returned by your SELECT statement in one value. For example, consider the code table in the demodb database that comes by default with CUBRID:

SELECT s_name FROM code;

The above SQL query returns six values:

s_name
------
X
W
M
B
S
G

But you want to see the column values from all returned rows as a single concatenated string like:

s_name
------
XWMBSG

Or sometimes separated by comma, comma and a space, or other delimiters like:

s_name
------
X, W, M, B, S, G

So, how do we do this in CUBRID?

CUBRID has a bulit-in function called SYS_CONNECT_BY_PATH() which takes two arguments: a column name, and a delimiter. This function returns a string that represents the concatenation of all the values obtained by the SELECT query.

SELECT SYS_CONNECT_BY_PATH(s_name, '') AS conc_s_name
FROM (SELECT ROWNUM AS r, s_name FROM code) AS res
START WITH r = 1
CONNECT BY PRIOR r = r - 1;

The above code will:

  1. Execute its subquery which returns results with two columns: the row number (using CUBRID's built-in ROWNUM() and the s_name column.
  2. Will concatenate all values of s_name column from all rows starting from row 1.
  3. CONNECT BY PRIOR will create a condition which column values should be concatenated. In this case, every previous column value should be concatenated to the current one.

As a result we will receive:

conc_s_name
------
X
XW
XWM
XWMB
XWMBS
XWMBSG

However, we want only the last value which represents the concatenation of all column values. So, we need to tell it to do so with the MAX() function, which will return the biggest value of the set.

SELECT MAX(SYS_CONNECT_BY_PATH(s_name, '')) AS conc_s_name
FROM (SELECT ROWNUM AS r, s_name FROM code) AS res
START WITH r = 1
CONNECT BY PRIOR r = r - 1;

This will return us what we need:

conc_s_name
------
XWMBSG

References

See also

CUBRID Query Tuning Techniques

This article has been written by one of the CUBRID core developers to help users improve their application performance by understanding h...

Obtaining Database Information in CUBRID

This guide explains how to retrieve database information from CUBRID. This includes: table, index, and column names, if certain columns are indexes ...

CUBRID Click Counter

The scope of this tutorial is to show you how to use one of the most special features implemented in CUBRID – Click Counter.




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: