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.