T-SQL – Query to get Distinct,Sorted, Comma Separated values in to a variable

Posted on February 2, 2015 by Prashanth Jayaram

CREATE  TABLE  #temp(name char(3))

INSERT INTO #temp VALUES ('CD')
,('AB')
,('LM')
,('BC')
,('GH')
,('KJ')
,('AB')

DECLARE @cols AS NVARCHAR(MAX);

SELECT @COLS =  substring(list, 1, len(list) - 1)
FROM   (SELECT list = 
          (SELECT DISTINCT name + ','
           FROM  #temp
           ORDER BY name + ','
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T

SELECT @COLS

DROP TABLE #temp

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/

View all posts by Prashanth Jayaram →

This entry was posted in T-SQL and tagged CSV, Distinct clause, Sorted and Order by clause, XML. Bookmark the permalink.

10 Responses to T-SQL – Query to get Distinct,Sorted, Comma Separated values in to a variable

NIngarajNayak says:

February 10, 2015 at 6:55 am

I think FOR XML PATH is not needed to achieve the above. This can be resolved as below as well.

CREATE TABLE #temp(name char(3))

INSERT INTO #temp VALUES (‘CD’)
,(‘AB’)
,(‘LM’)
,(‘BC’)
,(‘GH’)
,(‘KJ’)
,(‘AB’)

DECLARE @cols AS NVARCHAR(MAX)=”;

SELECT @cols=@cols+name+’,’
FROM #temp
GROUP BY name
ORDER BY name
SELECT @cols

DROP TABLE #temp

Reply
Åke Söderberg says:

February 10, 2015 at 7:40 am

Just to concatinate into a variable you don’t need to use FOR XML. Just concatinate in sql.

DECLARE @cols AS NVARCHAR(MAX)=”;

SELECT @cols = @cols + ‘,’ + name
FROM (SELECT DISTINCT name FROM #temp)a
ORDER BY name

SELECT STUFF(@cols,1,1,”)

Reply
igal says:

February 10, 2015 at 9:24 am

Hi.
I think there’s an easier way to do that:

CREATE TABLE #temp(name char(3))

INSERT INTO #temp VALUES (‘CD’)
,(‘AB’)
,(‘LM’)
,(‘BC’)
,(‘GH’)
,(‘KJ’)
,(‘AB’)

declare @v varchar(max)=”

select @v=@v + name + ‘,’ from #temp

select @v
drop table #temp

BTW, the same way you can do a summarizing or multiplying for a column:

CREATE TABLE #temp(name int)

INSERT INTO #temp VALUES (2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)

declare @v int = 1

select @v=@v * name from #temp

select @v

drop table #temp

Reply
Alan Knepper says:

February 10, 2015 at 1:30 pm

— Here is another slick way to handle this problem. See replacement SELECT statement.
— I can’t take credit for this, but here is a brief explanation of what’s going on.
— 1. When @cols is declared it starts off as NULL
— 2. The first iteration through @cols + ‘,’ = NULL (NULL + anything = NULL)
— 3. COALESCE turns that NULL result into ” so no comma is added before the first entry
— 4. ” is appended to T.name to start the string
— 5. @cols = ‘AB’ after the first iteration
— 6. Each subsequent pass ‘,’ is appended to @cols before appending T.name.
SELECT @cols = COALESCE(@cols + ‘,’, ”) + T.name
FROM #temp AS T
ORDER BY T.name

Reply
Tyler says:

February 10, 2015 at 1:48 pm

Hi Prashanth,

Good post! Here’s an alternative:

CREATE TABLE #temp(name char(3))

INSERT INTO #temp VALUES (‘CD’)
,(‘AB’)
,(‘LM’)
,(‘BC’)
,(‘GH’)
,(‘KJ’)
,(‘AB’)

DECLARE @cols AS NVARCHAR(MAX);

SELECT @cols = ISNULL(@cols + ‘,’, ”) + [name]
FROM #temp
WHERE [name] IS NOT NULL
GROUP BY [name]
ORDER BY [name]

SELECT @COLS

DROP TABLE #temp

Reply
KKelley says:

February 10, 2015 at 3:31 pm

Another approach without XML PATH.

CREATE TABLE #temp(name VARCHAR(3))

INSERT INTO #temp VALUES (‘CD’)
,(‘AB’)
,(‘LM’)
,(‘BC’)
,(‘GH’)
,(‘KJ’)
,(‘AB’)

DECLARE @cols AS NVARCHAR(MAX);

SELECT @cols = ISNULL(@cols + ‘,’, ”) + name
FROM (SELECT DISTINCT TOP 100 PERCENT name FROM #temp ORDER BY 1) x

SELECT @cols

DROP TABLE #temp

Reply
SQLMonger says:

February 10, 2015 at 5:41 pm

I don’t remember when I first came across this technique, but it was published back in the early 2000’s. I turned it into a code-generating procedure that spits out code you can use to create a User-Defined Function, so that the “flattener” logic could be used in report views to collapse lists of values. Below is the code example from a presentation I did for the St. Louis SQL Server Users Group some time ago:

use Adventureworks
go

/* There is simple and elegant use the Coalsce() function to “Flatten” a column
to a delimited string. I first saw the technique in an article in T-SQL Solutions.
At first Glance, I could not believe that it would work, but it does and it performs better
than using a cursor or while loop to perform the same operation:
*/
Declare @w_resulttext varchar(max)
SELECT @w_resulttext = coalesce( @w_resulttext +’, ‘, ”) + [Name]
FROM production.ProductSubCategory where ProductCategoryID = 4
select @w_resulttext

/*
The trick to this technique is to use the coalesce function to insert the delimiter.
Here is how it works:

When processing the first row, the variable is null, so the second term of the coalesce
function appends the “empty string” to the variable, then the value from the first row is appended
All subsequent rows are handled by the first term of the coalesce() function, resulting in the
delimiter being appended to the variable prior to adding the next row’s value.
I had written a bunch of user-defined functions using cursors to “flatten” or “Pivot” data
for several parent-child tables. To simplify converting them all to this technique,
I wrote a helper proc to generate the code for the technique.
It generates a functioning code “template” that can be used directly or modified by
adding conditions or joins to other tables:
*/
go
create procedure sp__CreateFlattener
@p_table sysname
,@p_column sysname
,@p_delimeter varchar(20) = ‘,’
,@p_SQL varchar(max) = null OUTPUT
as
/*
Generate code snippet for collapsing row values into a single delimited string
Author: @sqlmonger
*/
set @p_SQL = ‘Declare @w_resulttext varchar(max)
SELECT @w_resulttext = coalesce( @w_resulttext +’ + char(39)
+ @p_delimeter
+ char(39) + ‘, ‘
+ char(39) + char(39) + ‘) + ‘ + @p_Column
+ char(13) + char(10) + ‘FROM ‘ + @p_table
+ char(13) + char(10) + ‘select @w_resulttext’
–print @p_sql — comment this out if you don’t want the results printed to message window
return 0
go

— dynamic useage: Note it always sends text of query via print command
declare @SQL varchar(max)
exec sp__createflattener @p_table = ‘production.ProductSubCategory’, @p_column = ‘[Name]’, @p_delimeter = ‘|’, @p_SQL = @SQL OUTPUT
–exec (@SQL)
print @sql
go
/*
— generated script:
Declare @w_resulttext varchar(max)
SELECT @w_resulttext = coalesce( @w_resulttext +’|’, ”) + [Name]
FROM production.ProductSubCategory
select @w_resulttext

*/
Declare @w_resulttext varchar(max)
SELECT @w_resulttext = coalesce( @w_resulttext +’|’, ”) + [Name]
FROM production.ProductSubCategory
select @w_resulttext

–turn the output into a function that can be used in other queries:

create function dbo.fn_FlattenProductSubCategory (@ProductCategoryID int)
returns varchar(max)
begin
Declare @w_resulttext varchar(max)
SELECT @w_resulttext = coalesce( @w_resulttext +’, ‘, ”) + [Name]
FROM production.ProductSubCategory
where ProductCategoryID = @ProductCategoryID
return @w_resulttext
end
go

— simple select using the function
select dbo.fn_FlattenProductSubCategory(4)

–as part of a multi-row record set
select ProductCategoryID
, name as ProductName
,dbo.fn_FlattenProductSubCategory(ProductCategoryID) as SubCategories
from Production.ProductCategory

Reply
SQL Devil says:

February 10, 2015 at 6:12 pm

Minor (obvious) cleanup:
The proposed solution(s) result in “AB ,BC ,CD ,GH ,KJ ,LM” which contains extra spaces.
SELECT @cols = REPLACE (ISNULL(@cols + ‘,’, ”) + name, ‘ ‘ , ”)
results in more compact string: “AB,BC,CD,GH,KJ,LM”.

Reply
Elaena Bakman says:

February 10, 2015 at 6:59 pm

This is a similar idea to the original post, but simpler, I think:

CREATE TABLE #temp (name CHAR(3))
INSERT INTO #temp
VALUES (‘CD’)
,(‘AB’)
,(‘LM’)
,(‘BC’)
,(‘GH’)
,(‘KJ’)
,(‘AB’)
DECLARE @cols AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT ‘,’ + name
FROM #temp
ORDER BY name
FOR
XML PATH(”)
), 1, 1, ”)
SELECT @COLS
DROP TABLE #temp

Reply
EllieZ says:

November 10, 2015 at 6:35 pm

Exactly what I needed to generate a semi-colon delimited email list with no dupes – THX 🙂

Reply