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?

John Cappelletti · Accepted Answer · 2018-10-17 21:25:19Z

5

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

answered Oct 17, 2018 at 21:25

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kashif Qureshi · Accepted Answer · 2018-10-17 21:41:33Z

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

Collectives™ on Stack Overflow

Concatenate multiple columns separately across multiple rows in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related