Concatenating Text in Multiple Rows/Columns into a String Using STRING_AGG Function and Common Table Expressions (CTEs)

Concatenating Text in Multiple Rows/Columns into a String

Introduction

In this article, we will explore how to concatenate values from multiple rows and columns of a database table into a single string. We’ll use the STRING_AGG function along with Common Table Expressions (CTEs) to achieve this.

Problem Statement

We have a table called TEST with three columns: T_ID, S_ID, and S_ID_2. Each row represents a unique combination of values in these columns. The goal is to concatenate the values in S_ID and S_ID_2 into a single string for each group of rows that have the same T_ID, without grouping by any specific column.

Sample Data

;CREATE TABLE TEST (
  T_ID nvarchar(20),
  S_ID decimal(1,1),
  S_ID_2 int
)

INSERT INTO TEST (T_ID,S_ID, S_ID_2) VALUES 
('CHG123',0.1,1),
('CHG123',0.2,2),
('CHG124',0.1,1),
('CHG124',0.2,2),
('CHG124',0.3,3)

Desired Output

We want to convert the values in S_ID and S_ID_2 into a single string for each group of rows that have the same T_ID, separated by commas.

+--------+------+--------+-------------------+
|  T_ID  | S_ID | S_ID_2 |      OUTPUT       |
+--------+------+--------+-------------------+
| CHG123 |  0.1 |      1 |       0.1,0.2,1,2 |
| CHG123 |  0.2 |      2 |       0.1,0.2,1,2 |
| CHG124 |  0.1 |      1 | 0.1,0.2,0.3,1,2,3 |
| CHG124 |  0.2 |      2 | 0.1,0.2,0.3,1,2,3 |
| CHG124 |  0.3 |      3 | 0.1,0.2,0.3,1,2,3 |
+--------+------+--------+-------------------+

Solution

We will use two Common Table Expressions (CTEs): CTE_S_ID_G and CTE_S_ID_2_G. Each CTE will group the rows by T_ID, concatenate the values in S_ID and S_ID_2 respectively, using the STRING_AGG function.

WITH CTE_S_ID_G AS (
  SELECT T_ID
 , STRING_Agg(S_ID,',') WITHIN GROUP (ORDER BY S_ID) AS SID_G
 FROM TEST
 GROUP BY T_ID
), 
CTE_S_ID_2_G AS (
  SELECT T_ID
  , STRING_Agg(S_ID_2,',') WITHIN GROUP (ORDER BY S_ID_2) AS SID_2_G
 FROM TEST
 GROUP BY T_ID
)

Next, we will join the original table TEST with both CTEs on the T_ID column. We’ll use the CONCAT function to concatenate the values from SID_G and SID_2_G.

SELECT T.T_ID, T.S_ID, T.S_ID_2, CONCAT(sg.SID_G, ',', S2G.SID_2_G) AS OUTPUT
FROM TEST AS T
INNER JOIN 
CTE_S_ID_G AS SG
ON T.T_ID = SG.T_ID
INNER JOIN 
CTE_S_ID_2_G AS S2G
ON T.T_ID = S2G.T_ID;

This will give us the desired output:

+--------+------+--------+-------------------+
|  T_ID  | S_ID | S_ID_2 |      OUTPUT       |
+--------+------+--------+-------------------+
| CHG123 |  0.1 |      1 |       0.1,0.2,1,2 |
| CHG123 |  0.2 |      2 |       0.1,0.2,1,2 |
| CHG124 |  0.1 |      1 | 0.1,0.2,0.3,1,2,3 |
| CHG124 |  0.2 |      2 | 0.1,0.2,0.3,1,2,3 |
| CHG124 |  0.3 |      3 | 0.1,0.2,0.3,1,2,3 |
+--------+------+--------+-------------------+

Conclusion

In this article, we learned how to concatenate values from multiple rows and columns of a database table into a single string using the STRING_AGG function along with Common Table Expressions (CTEs). This approach allows us to achieve complex grouping and aggregation operations in a flexible and efficient manner.

Last modified on 2024-05-09