Concat multiple rows with dynamic table in SQL Server

Question

I have two tables where I joined both to get the first view, after that I'd like to do GROUP_CONCAT in one column, but we don't have this function in SQL Server.

So I tried some examples here, but was unsuccessful with my SQL. Examples found always use a table to generate GROUP_CONCAT, but I didn't find any example with a SQL result.

TableA:

| CityID | MasterID | Account |
+-----------------------------+
| 01 | 1234 | 1111 |
| 01 | 1234 | 2222 |
| 01 | 1234 | 3333 |
| 02 | 1234 | 4444 |

TableB:

| Account | Item | Price |
+------------------------+
|  1111   | A001 | 11,11 |
|  2222   | A001 | 11,11 |
|  3333   | A002 | 22,22 |
|  4444   | A002 | 22,22 |
|  5555   | A002 | 22,22 |

First SQL - Subselect:

SELECT * 
FROM TableA, TableB
WHERE TableA.AccountID = TableB.AccountID

Output:

| CityID | MasterID | Account | Account | Item | Price |
+------------------------------------------------------+
| 01 | 1234 | 1111 | 1111 | A0001 | 11,11 |
| 01 | 1234 | 2222 | 2222 | A0001 | 11,11 |
| 01 | 1234 | 3333 | 3333 | A0002 | 22,22 |
| 02 | 1234 | 4444 | 4444 | A0002 | 22,22 |

Second SQL - final view:

SELECT 
    MasterID, GROUP_CONCAT(Item)
FROM 
    (SELECT * 
     FROM TableA, TableB
     WHERE TableA.AccountID = TableB.AccountID) table01

Output:

| MasterID | Item |
+-----------------+
| 1234 | A0001,A002 |

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Sep 09 '16 at 04:55

score 0 · Accepted Answer · answered Sep 09 '16 at 03:41

This will give you expected result (I'm using Account as column name rather than AccountId as that's what you mentioned in table structure)

;WITH table01
AS
(
    SELECT A.CityId,A.MasterId,A.Account Account1,B.Account Account2,B.Item,B.Price
    FROM TableA A
    INNER JOIN TableB B
    ON A.Account = B.Account
)

SELECT A.MasterId,
STUFF(( SELECT ', ' + B.ITEM 
       FROM table01 B
       WHERE B.MasterId = A.MasterId
       group by B.MasterId,B.Item
       FOR XML PATH('')
     ), 1, 2, '') Item

FROM table01 A
GROUP BY A.MasterId

Concat multiple rows with dynamic table in SQL Server

1 Answers1