SQL query multiple rows into single row that has JOIN

Question

I am trying to combine multiple rows into a single row that involves a JOIN. I cannot figure how to get the JOIN piece to work.

I am on SQL Server 2014.

This piece works great where I am rolling rows into a single comma separated line based on ID_REVIEW:

SELECT DISTINCT
      ID_REVIEW
      , STUFF((
      SELECT N', ' + CAST(AREA_ID AS VARCHAR(255))
      FROM AREA_ASSOC t2
      WHERE t1.ID_REVIEW = t2.ID
      FOR XML PATH('')
    ), 1, 1, '') AS AREA_ID
FROM REQUEST_WORKLOAD t1

Result:

ID_REVIEW | AREA_ID
-----------------
    11438 | 2
    23501 | 10, 15
    44677 | 8

What I'd like to do is instead of having numbers for AREA_ID is have the name of that area show up. The goal being to have:

ID_REVIEW | AREA_NM
-----------------
    11438 | State St.
    23501 | Main St., Second St.
    44677 | Adams Ave.

This AREA_NM information is contained in another table, called AREA. In a separate query, I can do a LEFT JOIN on table AREA_ASSOC to pull in the AREA_NM:

SELECT DISTINCT
    [AREA_NM]
    FROM AREA_ASSOC 
    LEFT JOIN AREA ON
    AREA_ASSOC.AREA_ID = AREA.AREA_ID

I'm stumped as to how to get that JOIN into the STUFF function so I can get the area names. Is there a way to do this?

Gordon Linoff · Accepted Answer · 2019-09-05 14:22:19Z

3

Do the join in the subquery:

SELECT DISTINCT r2.ID_REVIEW,
      STUFF( (SELECT N', ' + a2AREA_NM 
              FROM AREA_ASSOC aa2 JOIN
                   AREA a
                   ON aa2.AREA_ID = A.AREA_ID
              WHERE aa2.ID_REVIEW = r2.ID
              FOR XML PATH('')
             ), 1, 2, ''
           ) AS AREA_ID
FROM REQUEST_WORKLOAD rw

answered Sep 5, 2019 at 14:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL query multiple rows into single row that has JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related