Concatenate multiple columns separately across multiple rows in SQL Server

Question

I need to concatenate two columns separately across multiple rows of data in SQL Server that are joined between two tables.

For instance, I have an "association" table and "market" table as shown below"

Association:

user_id |  role_id  | market_id
-------------------------------
1       |     a     |     1
1       |     a     |     2
2       |     c     |     3
2       |     c     |     4
2       |     c     |     5

Market:

market_id | market_name
-----------------------
1         | Arizona
2         | Utah
3         | Indiana
4         | Illinois
5         | Kentucky

I need to concatenate the market_id in the Association table into a single value, per user id, as well as bring in the concatenated market names from the Market table, with my desired output looking like:

user_id |  role_id  |  market_ids   | market_names
--------------------------------------------------
1       |     a     |     1,2       | Arizona,Utah
2       |     c     |    3,4,5      | Indiana,Illinois,Kentucky

Now, I am able to successfully concatenate the market id's but still end up with multiple rows of data for each market using the following SQL:

SELECT DISTINCT
    a.user_id,
    a.role_id,
    SUBSTRING(
        (
            SELECT DISTINCT ', ' + market_id  AS [text()]
            FROM Association aa
            WHERE a.user_id = aa.user_id
            FOR XML PATH ('')
        ), 2, 1000) [market_ids],
        SUBSTRING(
        (
            SELECT DISTINCT ', ' + market_name  AS [text()]
            FROM Market bb
            WHERE a.market_id = bb.market_id
            FOR XML PATH ('')
        ), 2, 1000) [Market Name]
FROM
    Association a
    INNER JOIN
    Market b
        ON a.market_id = d.market_id

Any ideas on how to achieve the second concatenation?

Answer 1

I prefer to use Stuff() over Substring(). Anyways, Basically for Market name, You should do your association join in your sub query and then join both association tables on users id. Try something like this:

SELECT DISTINCT
a.user_id,
    a.role_id,
    STUFF(
        (
            SELECT DISTINCT ', ' + cast(market_id  as varchar(25))
            FROM Association aa
            WHERE a.[user_id] = aa.[user_id]
            FOR XML PATH ('')
        ), 1, 1,'') [market_ids],
        STUFF(
        (
            SELECT DISTINCT ', ' + market_name 
            FROM Market bb
          Inner join Association aaa on aaa.market_id = bb.market_id
            WHERE a.user_id = aaa.user_id
            FOR XML PATH ('')
        ), 1, 1,'') [Market Name]
FROM  Association a 

Your original query can be written as:

SELECT DISTINCT
    a.[user_id],
    a.role_id,
    SUBSTRING(
        (
            SELECT DISTINCT ', ' + cast(market_id  as varchar(25)) AS [text()]
            FROM #Association aa
            WHERE a.[user_id] = aa.[user_id]
            FOR XML PATH ('')
        ), 2, 1000) [market_ids],
        SUBSTRING(
        (
            SELECT DISTINCT ', ' + market_name  AS [text()]
            FROM #Market bb
          INNER JOIN #Association aaa on aaa.market_id = bb.market_id
            WHERE a.user_id = aaa.user_id
            FOR XML PATH ('')
        ), 2, 1000) [Market Name]
FROM  #Association a

Answer 2

A CTE with a Row_Number() to keep the presentation sequence should do the trick

Example

;with cte as (
    Select A.*
          ,B.Market_Name
          ,RN = Row_Number() over (Partition By user_id,role_id order by a.market_id)
     From   Association A
     Join   Market B on A.market_id=B.market_id
)
Select Distinct
       user_id
      ,role_id
      ,market_ids   = stuff((Select concat(',',market_id  ) From cte Where user_id=A.user_id and role_id=A.role_id Order By RN For XML Path ('')),1,1,'')
      ,market_names = stuff((Select concat(',',market_name) From cte Where user_id=A.user_id and role_id=A.role_id Order By RN For XML Path ('')),1,1,'')
 From  cte A

Returns

user_id role_id market_ids  market_names
1       a       1,2         Arizona,Utah
2       c       3,4,5       Indiana,Illinois,Kentucky