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 Categoriesselect ‘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 Categoriesselect ‘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?
Could u plz tell me how to write it in Oracle plz.
Sorry. I am not sure of the syntax.
Great post. Coalesce is awesome!
[...] Coalesce can be really slick especially if you want to concatenate multiple rows into a single string. [...]
this is SICK
save my day
thanks a lot
i’ve been searching for a way to do this and counld find anything short of 30lines of code. You rock man!
I’m really glad I could help.
I Google another problem and I run into you again! Thanks!
@Michael – That’s good to hear. It seems that Google is just starting to give me some love.
Such a brief, wonderfull solution. Thank you very much for writing this post. Webpedia | Sydney PHP Coder
CCC OOO OOO L ||
C O O O O L ||
CCC OOO OOO LLL ..
@never_again – GGG LLL AAA DDD you liked it.
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 ..
i googled “t-sql concatenate multiple rows” and your page was the first result with the exact answer i needed. THANKS!
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!
I agree to all the comments above. Exactly what I was looking for!!!
You saved my a lot of time buddy. Thanks.
NAPLAN
Is there any way to do this using a single select query and not 2 select queries?
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
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
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.
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.
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.
GREAT!!!!!
YOU SAVED MY LIFE!!!
Hi, this is what I used in SQL2005,
Awesome post. Thanks!
Would ISNULL work in the same way as COALESCE in the above examples?
This came in very handy. Thank you.
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
Thanks very much – really helpful
Awesome!!
is it possible in the sql server 2008
Thanks Mr. Griswold… I should have known better ^^
@deep – works in SQL 2008, ofc.
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
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
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
Saved my day! Many thanks! Danke sehr! Muito obrigado!
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.
Really awesome. Saved me a lot of hassle on developing a very long and dynamic query.
Keep up the great work.
nice action. lol. that is as simple as crap
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
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?
Very useful code and innovative way, thanx
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
Excellent, you just saved me hours of work and made it possible to simplify my site. Awesome stuff.
Steven Chong — what a great and elegant solution. No variables necessary. No procedures/udfs necessary. Thanks!!
Bill