T-SQL query append columns

Question

I have a T-SQL query that returns a result of two rows with the same contact name but in one of the columns is different:

Contact Name   BuildingID    ClassID
------------------------------------
XXXX              720         3505
XXXX              720         3650

I need to combine both ClassID values into the ClassID column like this:

Contact Name   BuildingID    ClassID
----------------------------------------
XXXX              720        3505;3650

That's a SQL Anti Pattern. One field should contain one value. Why do you want to do this? — MatBailie, Sep 26 '16 at 13:51
Possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) --- Concatenating multiple values into a single field in a view is a pretty common requirements. Some RDBMS have built in functionality (Group_Concat in MySQL or string_agg in Oracle, for instance). With SQL Server you to use `stuff()` and `cross apply` to get the job done. Check out that link and it should get you close to what you need. — JNevill, Sep 26 '16 at 14:03

score 0 · Answer 1 · answered Sep 26 '16 at 14:43

You can try like this using stuff and xml Path

SELECT ContactName, BuildingID,
   STUFF((SELECT ','+CONVERT(varchar(20),classid) FROM Contacts where contactname= c.ContactName and BuildingId = c.BuildingID
           FOR XML PATH('')),1,1,'') AS ClassId
FROM Contacts C
Group by ContactName, BuildingId

score 0 · Answer 2 · answered Sep 26 '16 at 15:21

CREATE TABLE #XX
(
    ContactName VARCHAR(10) ,
    BuildingId INT,
    ClassId INT
)


INSERT INTO #XX VALUES ('XXXX',720,3505)
INSERT INTO #XX VALUES ('XXXX',720,3650)


SELECT DISTINCT ContactName,BuildingId,STUFF((
                SELECT  ';' + CAST(ClassId AS VARCHAR(10))
                FROM #XX FOR XML PATH('')
                ), 1, 1, '') AS ClassId
                FROM #XX

T-SQL query append columns

2 Answers2