Concatenate Row Values T-Sql

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

Concatenate row values T-SQL

Have a look at this

DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME
)

DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT
)

DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)

INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'

INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''

INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5

SELECT  *,
        ( 
            SELECT  u.FName + ','
            FROM    @Users u INNER JOIN 
                    @Reviewers rs ON u.UserID = rs.UserID
            WHERE   rs.ReviewID = r.ReviewID
            FOR XML PATH('')
        ) AS Products
FROM    @Reviews r

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 Query to concatenate row values based on condition

An example solution in MSSql:

SELECT r1.Column2 AS 'Column1', CONCAT(r1.Column3, ',', r2.Column3) AS Result
FROM Table r1
JOIN Table r2 ON r1.Column2 = r2.Column2
WHERE r1.Column1 LIKE '%Run%' AND r2.Column1 LIKE '%Total%'

Concatenate values of two consecutive rows SQL

Assuming that SerialNo defines your order, then you can use LAG() to get the value from the previous row:

SELECT  ProductName,
        Serial_No,
        CONCAT(Serial_No, LAG(Serial_No) OVER(ORDER BY Serial_No)) AS ConcatValue
FROM    YourTable;

If Serial_No doesn't define your order, then you can amend the order by as required.

best way to concatenate rows in sql

One way is to add a user-defined aggregate function, as an alternative for the missing STRING_AGG function in your version of MS SQL Server. Then replace the corrolated subquery with the function.

Examples of such a function can be found here and here

Another way, that doesn't involve a DB admin, is to use a temporary table with a composite index on the fields that are used to join in the sub-query with the FOR XML.

Test on db<>fiddle here

-- Just assuming the datatypes here, so change them to the correct types.
IF OBJECT_ID('tempdb..#tmpAUDITED') IS NOT NULL DROP TABLE #tmpAUDITED;
CREATE TABLE #tmpAUDITED 
(
  [ENTITY] INT NOT NULL,
  [ASSET NUMBER] INT NOT NULL,
  [YEAR AUDITED] INT NOT NULL,
  [COUNT] INT,
  [DATE] DATE,
  [USERNAME] VARCHAR(100),
  INDEX idx_1 NONCLUSTERED ([ENTITY], [ASSET NUMBER], [YEAR AUDITED])
);

INSERT INTO #tmpAUDITED (
[ENTITY], [ASSET NUMBER],[YEAR AUDITED], [COUNT], [DATE], [USERNAME]
)
SELECT 
[ENTITY], [ASSET NUMBER],[YEAR AUDITED], [COUNT], [DATE], [USERNAME]
FROM dbo.vwAUDITED;

-- Now using the temp table instead of the view.
SELECT
 [ENTITY],
 [ASSET NUMBER],
 [YEAR AUDITED],
 SUM([COUNT]) AS AUDITED,
 SUBSTRING
     (( 
        SELECT ', ' + s.[USERNAME]
        FROM #tmpAUDITED AS s
        WHERE s.[ENTITY] = [AUDITED].[ENTITY]
          AND s.[ASSET NUMBER] = [AUDITED].[ASSET NUMBER] 
          AND s.[YEAR AUDITED] = [AUDITED].[YEAR AUDITED]
        -- ORDER BY s.[ENTITY], s.[ASSET NUMBER], s.[YEAR AUDITED], s.[USERNAME]
        FOR XML PATH('')
     ), 2, 1000) AS [SCANNED BY],
 MAX([DATE]) AS [COMPLETION DATE]
FROM #tmpAUDITED AS [AUDITED]
GROUP BY [ENTITY], [ASSET NUMBER], [YEAR AUDITED];

Additionally, it might be worth it to check if the query in the view can be optimized. Or that adding certain indexes on the tables (used in the view) could improve the performance of the view itself.

---

Related Topics