CODEWITHSUNDEEP
sql-servert-sqlstring-aggregation
Bokbob
Bokbob

Reputation: 89

How to get single row values as comma appended text in SQL

In SQL my query results are like

enter image description here

I need to get results with my product name appended with ',' so that contact names are not duplicated.

Please help.

Upvotes: 0

Views: 156

Answers (2)

Irfan
Irfan

Reputation: 695

My Above answer is not applicable to older versions than SQL-Server 2017. So I have implemented some other solution as below.

Sample table: your_table

enter image description here

And apply this code:

select distinct tb2.first_name, 
    substring(
        (
            select ','+tb1.product_name  as [text()]
            from dbo.your_table as tb1
            where tb1.first_name = tb2.first_name
            order by tb1.first_name
            for xml path ('')
        ), 2, 500) [products]
from dbo.your_table tb2;

The result is here as per the expectations:

enter image description here

Upvotes: 1

Irfan
Irfan

Reputation: 695

SQL-Server 2017 (Not introduced for earlier versions)

Use string aggregation function STRING_AGG(). 

SELECT first_name+''+last_Name, STRING_AGG(product_name, ', ') AS products
FROM your_table
GROUP BY first_name,last_Name;

Add columns in GROUP BY according to your required output. Hope this will help you.

Upvotes: 0

Related Questions