TSQL Concatenate a column from join causing multiple rows

Question

I have what I thought at first was a simple concat issue and went to use STUFF and found my self running into a knowledge limitation as the multiple rows are coming from a join.

Most of the examples I was reading didn't have any filtering or somehow I missed how to implement it correctly.

I looked at this for example.

I'm strugling to figure out how I would get the effect of the join using a method such as the above SO post.

My query with multiple rows:

SELECT 
    [INC].[IN_ID],
    [INC].[SUBJ_LINE],
    [REL].[PEOPLE_ID],
    [ISA].[ISSUE_AREA_ID],
    '######' AS 'SEPERATOR',
    REL.*

FROM 
INCOMING INC

JOIN RELATION REL ON
REL.IN_ID = INC.IN_ID

JOIN [IN_ISSUE_AREA] ISA ON
ISA.IN_ID = INC.IN_ID

WHERE
   INC.METHOD = 'imail'
   AND [INC].[TO_ADDRESS] IS NOT NULL
   AND [INC].[IN_ID] = 5421121

The Results:

IN_ID | SUBJ_LINE | PEOPLE_ID | ISSUE_AREA_ID | SEPERATOR | .....
 542      SubjA      72          IssueA          ######
 542      SubjA      72          IssueJ          ######

What I would like to see is:

IN_ID | SUBJ_LINE | PEOPLE_ID | ISSUE_AREA_ID  | SEPERATOR | .....
 542      SubjA      72         IssueA, IssueJ    ######

Thank You

score 1 · Accepted Answer · answered Oct 10 '14 at 15:50

1

Try this:

SELECT 
    [INC].[IN_ID],
    [INC].[SUBJ_LINE],
    [REL].[PEOPLE_ID],
    STUFF( 
       (SELECT CAST(', ' + ISSUE_AREA_ID  AS VARCHAR(MAX)) 
         FROM IN_ISSUE_AREA
         WHERE (IN_ID= inc.IN_ID) 
         FOR XML PATH ('')
      ),1,2,'') 
    AS [ISSUE_AREA_ID],
    '######' AS 'SEPERATOR'

FROM 
INCOMING INC

JOIN RELATION REL ON
REL.IN_ID = INC.IN_ID

WHERE
   INC.METHOD = 'imail'
   AND [INC].[TO_ADDRESS] IS NOT NULL
   AND [INC].[IN_ID] = 5421121

answered Oct 10 '14 at 15:50

jpw

44,361
6
66
86

this worked and it is what I was initially attempting but I don't understand why it didn't work for me....well actually I do...because I'm not as smart as you. Anyway thank you and I'm going to parse through this and see what I wasn't doing correctly. – GPGVM Oct 10 '14 at 16:01
@user1278561 When you had a separate join for [IN_ISSUE_AREA] you got all the matching rows and hence multiple rows (one for each row in [IN_ISSUE_AREA]). Removing that join and using a correlated subquery makes sure you only get on row per IN_ID (in incoming, relation). – jpw Oct 10 '14 at 16:05
1

@jbw So...that is probably where I was falling down. In my mind I was adamant on using a join...how else am I going to make the match...but really by using the subquery in the select and being able to pass in a value INC.IN_ID from the main query I can achieve what I wanted....so probably old hat to you but that is cool and opens up a new way writing queries for me. – GPGVM Oct 10 '14 at 16:57

score 0 · Answer 2 · answered Oct 10 '14 at 15:59

Have you tried inserting the values into a temp table, or using a CTE, and then playing with the contents with the FOR XML PATH... concatenation trick? This is what the temp table might look like:

CREATE TABLE #This
( IN_ID         INT
 ,SUBJ_LINE     VARCHAR(50)
 ,PEOPLE_ID     INT
 ,ISSUE_AREA_ID VARCHAR(50)
 ,SEPERATOR     VARCHAR(50))

-- USE YOUR DATA AND JOINS TO INSERT THE INFO, IN PLACE OF THESE TWO LINES
INSERT #This VALUES (542,'SubjA',72,'IssueA','######')
INSERT #This VALUES (542,'SubjA',72,'IssueJ','######')

SELECT
     IN_ID
    ,SUBJ_LINE
    ,PEOPLE_ID
    ,LEFT(( SELECT ISSUE_AREA_ID + ', '
            FROM #This M2
            WHERE   M2.IN_ID = M1.IN_ID
                AND M2.SUBJ_LINE = M1.SUBJ_LINE
                AND M2.PEOPLE_ID = M1.PEOPLE_ID
                AND M2.SEPERATOR = M1.SEPERATOR
            ORDER BY ISSUE_AREA_ID
            FOR XML PATH('')
           )
        ,LEN((SELECT ISSUE_AREA_ID + ', ' 
              FROM #This M2
              WHERE M2.IN_ID = M1.IN_ID
                AND M2.SUBJ_LINE = M1.SUBJ_LINE
                AND M2.PEOPLE_ID = M1.PEOPLE_ID
                AND M2.SEPERATOR = M1.SEPERATOR
              ORDER BY ISSUE_AREA_ID
              FOR XML PATH('')
             ))-LEN(', '))              AS ISSUE_AREA_ID
    ,SEPERATOR
FROM #This M1
GROUP BY
     IN_ID
    ,SUBJ_LINE
    ,PEOPLE_ID
    ,SEPERATOR

DROP TABLE #This

TSQL Concatenate a column from join causing multiple rows

2 Answers2