SQL Query - Concatenating Results into One String
If you're on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF
trick:
DECLARE @CodeNameString varchar(100)
SELECT
@CodeNameString = STUFF( (SELECT ',' + CodeName
FROM dbo.AccountCodes
ORDER BY Sort
FOR XML PATH('')),
1, 1, '')
The FOR XML PATH('')
basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3
etc.) and the STUFF
puts a "nothing" character at the first character, e.g. wipes out the "superfluous" first comma, to give you the result you're probably looking for.
UPDATE: OK - I understand the comments - if your text in the database table already contains characters like <
, >
or &
, then my current solution will in fact encode those into <
, >
, and &
.
If you have a problem with that XML encoding - then yes, you must look at the solution proposed by @KM which works for those characters, too. One word of warning from me: this approach is a lot more resource and processing intensive - just so you know.
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
How to concat many rows into one string in SQL Server 2008?
Try this one -
DECLARE @temp TABLE (name NVARCHAR(50))
INSERT INTO @temp (name)
VALUES ('kim'),('lee'),('park'),('cho')
SELECT STUFF((
SELECT ',' + name
FROM @temp
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
Output -
kim,lee,park,cho
Concatenating results of SELECT query only returns one value
The question is indeed a duplicate. A comprehensive answer can be found in the following stackoverflow question: nvarchar concatenation / index / nvarchar(max) inexplicable behavior.
Several valid solutions to the problem of concatenating strings in a SELECT query are provided in this article: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
SQL, How to Concatenate results?
With MSSQL you can do something like this:
declare @result varchar(500)
set @result = ''
select @result = @result + ModuleValue + ', '
from TableX where ModuleId = @ModuleId
How to concatenate many rows with same id in sql?
In SQL-Server you can do it in the following:
QUERY
SELECT id, displayname =
STUFF((SELECT DISTINCT ', ' + displayname
FROM #t b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id
TEST DATA
create table #t
(
id int,
displayname nvarchar(max)
)
insert into #t values
(1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')
OUTPUT
id displayname
1 Editor, Reviewer
7 Editor, EIC, Reviewer
19 Editor, EIC, Reviewer
SQL Concatenate String in Result
You would use something like:
SELECT 'http://aurl.com/something?q=' + cast(anInteger as varchar) FROM table;
Related Topics
SQL Sum Data from Multiple Tables
Which SQL Query Is Faster? Filter on Join Criteria or Where Clause
Selecting Data into a Postgres Array
How to Insert Data into Two Tables Simultaneously in SQL Server
SQL Between Clause with Strings Columns
Check for File Exists or Not in SQL Server
Postgres Dynamic Query Function
SQL Server Equivalent to MySQL Enum Data Type
Left Join Turns into Inner Join
How to Query a Comma Separated Column for a Specific Value
MySQL - How to Front Pad Zip Code with "0"
How to Do a Max(Count(*)) in SQL
Splitting the String in SQL Server
SQL Server Operating System Error 5: "5(Access Is Denied.)"