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

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

CSV_Values

Advertisements

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/
This entry was posted in T-SQL and tagged , , , . Bookmark the permalink.

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

  1. NIngarajNayak says:

    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

  2. Åke Söderberg says:

    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,”)

  3. igal says:

    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

  4. Alan Knepper says:

    — 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

  5. Tyler says:

    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

  6. KKelley says:

    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

  7. SQLMonger says:

    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

  8. SQL Devil says:

    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”.

  9. Elaena Bakman says:

    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

  10. EllieZ says:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s