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 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 PRIORwill 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
- ROWNUM/INST_NUM() Function
- MAX Function
- Functions Available When Using the CONNECT BY Clause
- START WITH ... CONNECT BY Clause
- GROUPBY_NUM() Function
- ORDERBY_NUM() Function
- LIMIT Clause
This article has been written by one of the CUBRID core developers to help users improve their application performance by understanding h...
This guide explains how to retrieve database information from CUBRID. This includes: table, index, and column names, if certain columns are indexes ...
The scope of this tutorial is to show you how to use one of the most special features implemented in CUBRID – Click Counter.