TSQL: Concatenate Multiple Rows into String

In T-SQL, there’s an easy way to concatenate multiple rows into a single string.

If you simply need to munge the results together, here’s the down and dirty solution:

use Northwind

declare @CategoryList varchar(1000)
set @CategoryList = ”
select @CategoryList = @CategoryList + CategoryName from Categories

select ‘Results = ‘ + @CategoryList

————————————————————————————————–
Results = BeveragesCondimentsConfectionsDairy ProductsGrains/CerealsMeat/PoultryProduceSeafood

(1 row(s) affected)

However, if you want to generate a pretty delimited string and gracefully handle null values, take advantage of the coalesce function (which I absolutely love to the point of overuse.)

use Northwind

declare @CategoryList varchar(1000)
select @CategoryList = coalesce(@CategoryList + ‘, ‘, ”) + CategoryName from Categories

select ‘Results = ‘ + @CategoryList

—————————————————————————————————————–
Results = Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce, Seafood

(1 row(s) affected)

Do you need a great SQL interview question? Ask your next candidate how the T-SQL “COALESCE” function used?

Comments

  1. i’ve been searching for a way to do this and counld find anything short of 30lines of code. You rock man!

  2. Hi,
    Can you tell me how can i get all the rows of a table(including all columns ) into a variable of varchar kind .
    you did this for a single column value ..

  3. i googled “t-sql concatenate multiple rows” and your page was the first result with the exact answer i needed. THANKS!

  4. I googled TSQL Concatenate Rows and ended up here on the second click. This is by far the easiest, cleanest, and quickest solution out there! Another page had some really complex TSQL to do this involving pivot tables and such. COALESCE is much nicer!

  5. If you only need to see the results without the “Results =” label, you need only declare the variable and execute the single query:

    declare @CategoryList varchar(1000)
    select @CategoryList = coalesce(@CategoryList + ‘, ‘, ”) + CategoryName from Categories

  6. This is a great function!

    I have a query that produces several values for one person in my records. For example if you have a table with marathon times for more than one person. Each person has multiple entries:

    PersonID Time
    1 04:32:23
    1 04:30:24
    2 05:01:54
    2 04:56:04

    I would like to produce:

    PersonID Time
    1 04:32:23, 04:30:24
    2 05:01:54, 04:56:04

    Thanks!
    Serwar

  7. I’d love to use this code, but am concerned about it breaking. There is detailed discussion of this problem here, where it is suggested that this solution can be unreliable.

  8. Great code.
    Well, now I need to extend the code in order to obtain the products string for each category in a single result. The result should appear as the following

    category1, products_string1
    category2, products_string2
    ...
    categoryN, products_stringN

    Thanks.

  9. I’ve found a solution:

    DECLARE @CatId int
    DECLARE cur CURSOR FOR SELECT CategoryId from Categories
    DECLARE @ProdList varchar(1000)
    OPEN cur
    FETCH NEXT FROM cur
    INTO @CatId
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @ProdList = ''
    select @ProdList = coalesce(@ProdList + ', ', '') + ProductName
    from Products
    where CategoryId = @CatId
    order by ProductName
    print cast(@CatId as varchar(50)) + @ProdList
    FETCH NEXT FROM cur
    INTO @CatId
    END
    CLOSE cur
    DEALLOCATE cur

    But it uses cursors! I’m sure there’s a briefier solution.

  10. Hi, this is what I used in SQL2005,

    SELECT ‘Results = ‘ + Stuff((
    SELECT N’ ‘ + CategoryName FROM Categories FOR XML PATH(”),TYPE
    ).value(‘text()[1]‘,’nvarchar(max)’),1,1,N”);

  11. Here is the logic applied as a function, returning a distinct, concatenated list of deliverables for each combination of Program Name and Due Date:

    CREATE FUNCTION [dbo].[fn_Deliverables]
    (
    @ProgramName varchar(50),
    @DueDate datetime
    )
    RETURNS varchar(500)
    AS
    BEGIN
    DECLARE @Deliverables varchar(500)

    SELECT @Deliverables = COALESCE(@Deliverables + ‘, ‘, ”) + [Deliverable Name]
    FROM (

    SELECT DISTINCT [Deliverable Name] FROM [Registered Deliverables Schedule] WHERE [Program Name] = @ProgramName AND [Due Date] = @DueDate
    ) AS Distinct_List

    RETURN @Deliverables

    END

  12. ANy way to do this without the variable? For instance let’s say that I have following table:

    Owner Asset
    Bob Cat
    Bob Dog
    Bob Bird
    Sally Dove
    Rich Mouse
    Rich Rat

    Without the variable I’d like to be able to use it in a query that is doing it in a group to return
    Bob Cat,Dog,Bird
    Sally Dove
    Rich Mouse,Rat

  13. The trick is not in coalesce since you can use isNull instead
    but the trick is in using the nested variable as
    select @i = @+ 1 from ….

    Thanks though

  14. The trick is not in coalesce since you can use isNull instead
    but the trick is in using the nested variable as
    select @i = @i + 1 from ….

    Thanks though

  15. Hi,

    Please help me with below req:
    id
    11,25
    15,78
    78,25
    11,15
    89,67

    i have to update the id 11 as 1,25 as 200,15 as 30 and some values

    Output should be
    new_Id
    1,200
    30,225
    225,200
    …….

    Please help me with this.

  16. This is really cool, help me out i have a report which has to look like this:

    ———COMPANY MEGA
    ———————————————–
    ShareHolders: |Boby, John, Jimm|

    Declare @string varchar(max)
    Set @string = ' '
    Select @string = @string +','+ ([FirstNames]+' '+ Surname)
    from ShareHolder

    Select @string

    But The thing is, shareholders has a foriegn key CompanyID
    which links to Company.CompanyID

    so when a company is selected, the shareholders will be given for that company.
    How will i do that? should i add like a :
    Inner join
    Company
    ON
    ShareHolder.CompanyID = Company.CompanyID

    Somewhere? and what about a @CompanyID parameter?

    The help will be so much appreciated thank you

  17. Hi,

    I have an issue where I have a value, say CT Brain W/O Contrast that I need to compare with multiple other single or multiple worded values in a Dictionary table, and if they match, trigger an event..

    Example:

    I want to notify a doctor that he has a new case to read. He reads all Brain and related exams. So my dictionary list would contain words like:

    BRAIN
    NECK
    CERVICAL
    C-SPINE
    NEURO

    The actual exam name comes in as CT BRAIN W/O & W/ Contrast. I need a way to compare the CT BRAIN W/O & W/ Contrast with each value in the dictionary above, and if one word (in this case BRAIN) matches, I need to trigger an event. I have everything in place already, however, I have to manually add OR clauses to the trigger each time a new Dictionary value is given to me. I want to be able to just place the new word into the table, and using Coalesce or something similar generate a string like such:

    SELECT @IsNeuro = CASE WHEN @ExamName LIKE ‘%NEURO + @ExamStringList + ‘%’ THEN ‘True’ ELSE ‘False’ END

    WHERE @ExamStringList is something like this:

    %’ OR @ExamName LIKE ‘%BRAIN%’ OR @ExamName LIKE ‘%NEURO%’ OR @ExamName LIKE ‘%NECK%’ OR @ExamName LIKE ‘%C-SPINE%’ OR @ExamName LIKE ‘%CERVICAL

    The issue I am running into is that my function is not making @ExamName a variable, instead it resulting in a text format, so no variable is pulled in for comparison.

    Does this make any sense? Essentially I need to figure out a way to compare the exam name to each word that matches in the dictionary list, and if at least one match exists, then I would trigger my event. Any thoughts?

  18. Here is the method that I use. It allows you to order the items and optionally to use delimiters, and it does not rely on coalesce.

    DECLARE @result VARCHAR(MAX) =”
    DECLARE @delim VARCHAR(10)
    DECLARE @count INT

    –Make test data
    DECLARE @T TABLE(col VARCHAR(MAX))
    INSERT INTO @T VALUES(‘world’)
    INSERT INTO @T VALUES(‘concatenate’)
    INSERT INTO @T VALUES(‘hello’)
    INSERT INTO @T VALUES(‘sql’)

    – Example 1: simple concatenation
    – Result: concatenatehellosqlworld
    SET @result = ”
    SELECT @result = @result + col
    FROM (
    SELECT TOP 999999999
    col
    FROM
    @T
    ORDER BY col
    ) T

    SELECT @result

    – Example 2: separate with interior delimiter
    – Result: concatenate:hello:sql:world
    SET @result = ”
    SET @delim = ”
    SELECT
    @result = @result + @delim + col,
    @delim = ‘:’
    FROM (SELECT TOP 999999999 col FROM @T ORDER BY col) T

    SELECT @result

    – Example 3: separate with interior delimiter and penultimate delimiter
    – Result: concatenate, hello, sql and world
    SET @result = ”
    SET @delim = ”
    SET @count = (SELECT COUNT(col) FROM @T)

    SELECT
    @result = @result + CASE @count WHEN 1 THEN ‘ and ‘ ELSE @delim END + col,
    @count = @count – 1,
    @delim = ‘, ‘
    FROM (SELECT TOP 999999999 col FROM @T ORDER BY col) T

    SELECT @result

closed