Sql, How to Concatenate Results

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

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.

SQL Concatenate String in Result

You would use something like:

SELECT 'http://aurl.com/something?q=' + cast(anInteger as varchar) FROM table;

Concatenate results from a SQL query in Oracle

-- Oracle 10g --

SELECT deptno, WM_CONCAT(ename) AS employees
  FROM   scott.emp
GROUP BY deptno;

Output:
     10  CLARK,MILLER,KING
     20  SMITH,FORD,ADAMS,SCOTT,JONES
     30  ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL Server Concatenate Query Results

Try this

CREATE TABLE #Tmp
    (
      id INT ,
      xValue VARCHAR(10)
    )

INSERT  INTO #Tmp VALUES  ( 1, 'x' )
INSERT  INTO #Tmp VALUES  ( 1, 'y' )
INSERT  INTO #Tmp VALUES  ( 1, 'Z' )
INSERT  INTO #Tmp VALUES  ( 2, 'A' )
INSERT  INTO #Tmp VALUES  ( 2, 'B' )


SELECT  id ,
        ( STUFF(( SELECT DISTINCT
                            ',' + CAST(xValue AS VARCHAR(20))
                  FROM      #Tmp
                  WHERE     id = t.id
                FOR
                  XML PATH('')
                ), 1, 1, '') ) AS Data
FROM    #Tmp t
GROUP BY id

concatenate results from subquery into a single value

To do it into a variable this will put single qutes around each value and a comma seperator:

DECLARE @OUTPUT as varchar(MAX)
Set @OUTPUT = ''

SELECT @OUTPUT = @OUTPUT + case when @OUTPUT = '' then '' else ', '  end + '''' + rtrim(TableValue) + ''''
    FROM dbo.Table

select @OUTPUT

-- or you can do it inline like this:

Select STUFF((
    SELECT ',' + TableValue    
    FROM dbo.TableLookup
    FOR XML PATH('')), 1, 1,'') 
FROM dbo.TableLookup

SQL Server Concatenate results on one line

you need to add group by at the end as below:

select [Date] as PurDate, [client_ID], 
 AllDetails =
 STUFF (
  (select ', ' + Details  FOR XML PATH ('')), 1, 1, '' 
 ) 
from (select...
) a
group by [Date], [Client_Id]

Created with your sample data and you can query as below:

Select [Date], Client_id, 
    Stuff((Select ','+Item_desc from #alldata where [Date] = a.[Date] and [Client_id] = a.Client_id for xml path('')), 1,1,'')
    from #alldata a
    group by [Date], Client_id

Input table:

create table #alldata ([Date] date, Client_id int, Item_desc varchar(15))

insert into #alldata([date], Client_id, Item_desc) values
 ('2017-02-01', 12  ,'GLOVES')
,('2017-02-01', 12  ,'HAT')
,('2017-02-01', 12  ,'SHOES')
,('2017-02-01', 25  ,'GLOVES')

Output as below:

+------------+-----------+---------------------+
|    Date    | Client_id |      Item_Desc      |
+------------+-----------+---------------------+
| 2017-02-01 |        12 |  GLOVES, HAT, SHOES |
| 2017-02-01 |        25 |  GLOVES             |
+------------+-----------+---------------------+

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

---

Related Topics