How to use STRING_AGG to concatenate strings with grouping

23 December,2020 by Jack Vamvas

Question:  Below is table and expected result. What is the query to achieve this result
Table : Test

ID LOT
7065161 4
7065212 1
7065212 4
7065203 1
7065203 2
7065203 3


Expected Result of query

ID LOT
7065161 4
7065212 1_4
7065203 1_2_3

 

Answer: Previous to SQL Server 2017 - I use to use the COALESCE function , but since SQL Server 2017 - a new function was introduced : STRING_AGG . According to MS documentation "Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string"

There is no better way to illustrate STRING_AGG than with an example query.

 

 

create table #t1
(ID char(7),LOT char (1))
INSERT INTO #t1
select '7065161','4'
UNION
select '7065212', '1'
UNION
select '7065212','4'
UNION
select '7065203','1'
UNION
select '7065203', '2'
UNION
select '7065203', '3'

SELECT ID, STRING_AGG(LOT, '_') as LOT
FROM #t1
GROUP BY ID;


drop table #t1

--------RESULTS----------
ID	LOT
7065161	4
7065203	1_2_3
7065212	1_4

Read more string functions 

How to write a conditional substring in sql

SQL Server Stuff and comma delimited column

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use STRING_AGG to concatenate strings with grouping


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer