Sample Header Ad - 728x90

Query that flattens many tables, but now want to concatinate one set of strings into a single string

-2 votes
1 answer
33 views
I have an SP that flattens a bunch of tables. I recently refactored so that instead of a single description in one table I now have a one to many relationship with a descriptions table. I can inner join and get the descriptions on many rows, but I'd rather just concatenate the descriptions into a single field. I'm trying STRING_AGG, but I don't think it works for my purposes. Here's the working query that just gives me each description on its own row and commented out what I tried to concatenate (adding a comma to the end of the line above as well). I don't really grok SQL well enough to even know how to proceed . . . Select P.ID as ProjectID, P.Code, P.MOAProjectname, P.Contractorname, P.Ownername, M.ID as PeopleID, M.Firstname + ' ' + M.Lastname As Fullname, R.ID as ReportID, R.Reportnumber, R.Reportdate, R.Weather, R.Temperature, R.Presentatsite, R.Estimatedcomplete, R.Progress, R.Trades, R.Note, D.DivID, D.DivName, O.ID as ObsID, O.Description, O.PicCaption, O.Opendate, O.Closedate, O.ImagePath, O.ObservationNum --STRING_AGG (T.Description,', ') AS Dsc From Observations as O inner Join Reportinformation as R on O.ReportID = R.ID Inner Join Descriptions as T on O.ID = T.ObsID Inner Join Divisions as D on O.Division = D.DivID Inner Join MOApeople as M on M.ID = R.MOApeopleID Inner Join Projectinformation as P on P.ID = R.ProjectID Order By P.Code, O.Division, O.ObservationNum I am guessing that I could create a temp table that does the aggregation, and then join that table into my main query. Is that the right direction to pursue?
Asked by Paul Gibson (149 rep)
Feb 12, 2021, 11:49 PM
Last activity: Feb 13, 2021, 07:08 AM