How do I join two tables on a common column name where 1 table has multiple rows of text I need to concatinate?

Question

I'm trying to return one row result on a.OrderNumber = b.OrderNumber where table b has multiple lines of service comments. Will I need to concatenate the comments into one field result to prevent multiple rows and how do I do that? Here is what I have.

SELECT a.OrderNumber,
    b.Comment,
    b.Comment,
    b.DATE
FROM Orders a
LEFT JOIN Comments b ON a.OrderNumber = b.OrderNumber

I'm looking for:

   OrderNumber            Comment
   1200         01-01-13 Repair made, 01-02-13 Billed Customer

What I get is:

OrderNumber            Comment            Date
1200              Repair made        01-01-13
1200              Billed Customer    01-02-13

Here's the result I currently have:

enter image description here

I seem to remember there being many answers to this type of question on SO already, did you search for a solution? — Tony, Nov 06 '13 at 19:17
http://stackoverflow.com/questions/10553198/merge-multiple-rows-into-a-single-row/10553404#10553404 — SQLMason, Nov 06 '13 at 19:44
possible duplicate of [SQL Server : join and append columns](http://stackoverflow.com/questions/12624382/sql-server-join-and-append-columns) — Tony, Nov 06 '13 at 21:39

David · Accepted Answer · 2013-11-07T15:43:11.037

Since the OP seemed to have difficulty with the provided code sample I have updated the answer to include the full code result seeing as how the question now includes enough information to make an approximation of the underlying schema.

Incidentally, try not to use reserved words as column names.

CREATE TABLE Orders (OrderNumber INT);
GO
CREATE TABLE Comments (OrderNumber INT, Comment VARCHAR(255), CommentDate DATETIME);
GO

INSERT INTO Orders VALUES (1),(2);
INSERT INTO Comments VALUES
    (1, 'Stuff', GETDATE()),
    (1, 'Other Stuff', GETDATE()),
    (2, 'More stuff', GETDATE())

;WITH cteOrderComment AS (
    SELECT a.OrderNumber
        ,STUFF((
            SELECT CONVERT(VARCHAR, C.CommentDate, 10) + ' ' + Comment + ', '
            FROM Comments C
            WHERE C.OrderNumber = A.OrderNumber
            FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR (MAX)')
        ,1,0,'') [Comments]
    FROM Orders a
    GROUP BY a.OrderNumber
)

SELECT T.OrderNumber, LEFT(T.Comments, LEN(T.Comments) - 1) [Comments]
FROM cteOrderComment T
GO

DROP TABLE Orders
DROP TABLE Comments

How do I join two tables on a common column name where 1 table has multiple rows of text I need to concatinate?

1 Answers1