-2

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?

Paul Gibson
  • 149
  • 6

1 Answers1

1

After much playing around (I find that SQL errors are difficult to parse) I realized that my original thought was correct but it took a while to get the syntax correct.

First use a select in a with clause to concatenate the rows along with a group by so that the descriptions are all collected with the right parent (ObsID), and then do the large query with multiple joins, including on the new table:

With FlatDesc AS (
Select ObsID, STRING_AGG(T.Description,'  |  ') As Dsc
From Descriptions as T
GROUP BY ObsID
)
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.PicCaption, O.Opendate, O.Closedate, O.ImagePath, O.ObservationNum, FD.Dsc as Description
    --STRING_AGG (T.Description,', ') AS Dsc
From Observations as O inner Join Reportinformation as R on O.ReportID = R.ID
Inner Join FlatDesc as FD on O.ID = FD.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
Paul Gibson
  • 149
  • 6