We can concatenate string columns with STRING_AGG function. STRING_AGG function requires 2 argument. Thats why we must put seperater between values.
Let’s make examples for a better understanding of STRING_AGG Function.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 |
CREATE TABLE [dbo].[MyTable] ([ID] INT IDENTITY, [Name] VARCHAR(20) NULL, [Age] INT NULL) INSERT [dbo].[MyTable] VALUES ('Hakan GURBASLAR',36),('Nurullah CAKIR',34),('Faruk ERDEM',27),(null,27) |
If you execute the following query you will receive the below error.
|
1 |
SELECT STRING_AGG ([Name]) FROM [dbo].[MyTable] |
Msg 174, Level 15, State 1, Line 9
The STRING_AGG function requires 2 argument(s).
STRING_AGG Function Usage:
It has two parameters. The first one is the name of your column that you want to concatenate. The second one is the seperator value. You can see how we can use this function below.
As you can see in the below results, null values didnt return as a result.
|
1 2 |
SELECT STRING_AGG ([Name], ' ') FROM [dbo].[MyTable] SELECT STRING_AGG ([Name], ',') FROM [dbo].[MyTable] |
If you want you can replace null values with the below script:
|
1 |
SELECT STRING_AGG (ISNULL([Name],'NullValue'), ',') FROM [dbo].[MyTable] |
If you want to concatenate more than one column you can use the below script:
|
1 |
SELECT STRING_AGG (CONCAT(ISNULL([Name],'NullValue'), '-', [Age]), ',') FROM [dbo].[MyTable] |
