SQL Query using FOR XML PATH that works right

Question

I have read the many posts regarding collapsing multiple table columns into a single column using FOR XML PATH, but I am not a database person and cannot make other posts' suggestions work. I expect my problem is easy to solve for an experienced SQL DB person.

I have MS SQL Server 2012. I have 3 tables (table1, table2, table3). The middle table (table2) creates a one-to-many relationship between table1 and table3 like this:

TABLE1.eventId
        \ _________________ one-to-one (on eventId)
         \
       TABLE2.eventId _____ creates one-to-many relationship
       TABLE2.valueId
               \ __________ one-to-one (on valueId)
                \
              TABLE3.valueId
              TABLE3.stringValue

My select statement:

SELECT TABLE1.eventId, TABLE2.eventId, TABLE2.valueId, TABLE3.valueId AS myValueId, TABLE3.stringValue
FROM  TABLE1
  INNER JOIN TABLE2 ON TABLE1.eventId = TABLE2.eventId
  INNER JOIN TABLE3 ON TABLE2.valueId = TABLE3.valueId
ORDER BY TABLE1.eventId DESC

Of course, the result is:

187252  187252  3   3   SomeString1 -\__ I WANT TO COMBINE INTO ONE
187252  187252  9   9   SomeString2 -/   COLUMN ON A SINGLE LINE
187251  187251  3   3   SomeString1
187251  187251  14  14  SomeString3

What I really want is something like this:

187252  SomeString1 SomeString2
187251  SomeString1 SomeString3

I have tried this sort of query using FOR XML PATH:

SELECT TABLE1.eventId, TABLE3.stringValue,
  SUBSTRING((SELECT mt2.eventId, mt2.valueId
             FROM  TABLE2 AS mt2
             WHERE mt2.valueId = TABLE3.valueId
             ORDER BY mt2.eventId
             FOR XML PATH('')), 3, 2000) AS JoinedValue
FROM  TABLE1, TABLE3
ORDER BY TABLE1.eventId DESC

But it returns multiple lines with the same valueId and different strings. I think I simply don't understand database fundamentals well enough to make FOR XML PATH work for me.

Loads of appreciation given to all for not beating me up about duplicate posts. And, of course, for clues leading me to a solution.

(Please note that server-side programming is not an option, for programmatic reasons. And this must be done in a single query.)

Many thanks for your thoughts.

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

Try the method in this post. It's one of my favorites.

SELECT 
  eventID,
  STUFF((
    SELECT ', ' + stringValue 
    FROM table1 t2
    WHERE (t2.eventID = t1.eventID) 
    FOR XML PATH(''))
  ,1,2,'') AS NameValues
FROM table1 t1
GROUP BY eventID

edited May 23 '17 at 10:26

Community

1
1

answered Jun 16 '15 at 20:32

MartianCodeHound

355
1
8

Not sure you need the .value part. I've used this method quite a few times and never needed to add that. – JamieD77 Jun 16 '15 at 20:41
Using SSMS, I get an error that I can't pass XML to the STUFF function if I try to run it without the .value bit. – MartianCodeHound Jun 16 '15 at 20:47
1

That's because you're using `TYPE`. Get rid of that and it should work – JamieD77 Jun 16 '15 at 20:59

JamieD77 · Accepted Answer · 2015-06-16T21:00:58.890

Similar to @MartianCodeHound I'd use STUFF

SELECT  t1.eventId,
        STUFF((SELECT  ',' + t3.stringValue
               FROM     TABLE2 t2
                        JOIN TABLE3 t3 ON t2.valueId = t3.valueId
               WHERE    t2.eventId = t1.eventId
               ORDER BY t3.stringValue
               FOR XML PATH('')), 1, 1, '') AS stringValue
FROM    TABLE1 t1

Here's a test for you

DECLARE @TABLE1 TABLE (eventId INT)
DECLARE @TABLE2 TABLE (eventId INT, valueId INT)
DECLARE @TABLE3 TABLE (valueId INT, stringValue VARCHAR(255))
INSERT INTO @TABLE1 VALUES (1), (2), (3)
INSERT INTO @TABLE2 VALUES(1, 1),(1, 2), (2, 1), (3, 1)
INSERT INTO @TABLE3 VALUES (1, 'StringValue1'), (2, 'StringValue2')

SELECT  t1.eventId,
        STUFF((SELECT  ',' + t3.stringValue
               FROM     @TABLE2 t2
                        JOIN @TABLE3 t3 ON t2.valueId = t3.valueId
               WHERE    t2.eventId = t1.eventId
               ORDER BY t3.stringValue
               FOR XML PATH('')), 1, 1, '') AS stringValue
FROM    @TABLE1 t1

score 0 · Answer 3 · answered Jun 16 '15 at 23:20

Most people use subqueries because that's what they are used to. Personally, I like to use CROSS APPLY which in this case, acts the same way, it's just a little easier to read. Check it out:

SELECT  t1.eventId,
        STUFF(xml_col, 1, 1, '') AS Concatenated_Column
FROM    TABLE1 AS t1
CROSS APPLY (
                SELECT  ' '/*This is your delimiter*/ + t3.stringValue
                FROM TABLE2         AS t2
                INNER JOIN   TABLE3 AS t3 
                    ON t2.valueId = t3.valueId
                WHERE    t2.eventId = t1.eventId
                ORDER BY t3.stringValue
                FOR XML PATH('')
            ) AS CA(xml_col)

score -2 · Answer 4 · answered Jun 16 '15 at 20:52

-2

 (SELECT  SUBSTRING(stringValue,1,2000) + ', '
        FROM  TABLE2 AS mt2
         WHERE mt2.valueId = TABLE3.valueId
        FOR XML PATH('') )  as JoinedValue

answered Jun 16 '15 at 20:52

vj78

53
2
7

SQL Query using FOR XML PATH that works right

4 Answers4