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:

Execute its subquery which returns results with two columns: the row number (using CUBRID's built-in ROWNUM() and the s_name column.
Will concatenate all values of s_name column from all rows starting from row 1.
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

How to Concatenate Column Values from Different Rows

References

See also

CUBRID Query Tuning Techniques

Obtaining Database Information in CUBRID

CUBRID Click Counter