There is the following database schema

enter image description here

Please tell me how on the server side to glue the performers of the application for the given scheme?

What I tried to do myself:

SELECT r.Id AS RequestId, r.OpeningDate, rh.Note FROM Requests r INNER JOIN (SELECT * FROM RequestHistories rh WHERE rh.Id IN (SELECT MAX(_rh.Id) AS Id FROM RequestHistories _rh GROUP BY _rh.RequestId)) rh ON r.Id = rh.RequestId INNER JOIN RequestHistoryPerformers rhp ON rh.Id = rhp.RequestHistoryId WHERE r.ClosingDate IS NULL

With this query, I start to collect the presentation I need, but I cannot understand how to add the LastName from the Performers table to the resulting query through the separator, i.e. get something like Ivanov, Petrov

Tried to do with for xml path :

 Select r.Id as RequestId , r.OpeningDate , (select _p.LastName + ',' from performers _p where _p.Id=rhp.PerformerId for xml path('')) as Performers

but unfortunately could not achieve the desired result

Sqlfiddle example

    2 answers 2

    If I understand correctly, then you need to make RequestHistoryPerformers in the subquery where the stitching of the strings occurs.

    Something like this:

     SELECT r.Id AS RequestId, r.OpeningDate, rh.Note, Performers = stuff(( select ', ' + _p.LastName from performers _p join RequestHistoryPerformers rhp ON _p.Id = rhp.PerformerId where rhp.RequestHistoryId = rh.Id order by _p.LastName for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') FROM Requests r CROSS APPLY ( SELECT TOP 1 rh.* FROM RequestHistories rh WHERE rh.RequestId = r.Id ORDER BY rh.Id DESC ) rh WHERE r.ClosingDate IS NULL

    You can make a subquery in OUTER APPLY , as advised in the next answer.

    Also, from RequestHistories you take the last entry for this RequestId . I would do it a little differently.

    • The cross-inquiry is clear to me, but what is the difference in the ways of receiving both you and me? but the magic in stuff is completely incomprehensible to me. Now I will try to smoke an Internet - Bald
    • @Bald, "ways to get" - do you mean gluing? Or refer to RequestHistories ? - i-one
    • I meant getting the last entry for the application from the history, i.e. the current state you have made it through a cross-query ... - Bald
    • @Bald, in your variant RequestHistories will participate in the query plan twice (refer to the table and another one, where is the grouping). In my version there will be one appeal, i.e. The plan should be a little more effective. - i-one
    • @Bald, yes, and I have a bug in there, instead of WHERE rh.RequestId = rh.Id should be WHERE rh.RequestId = r.Id (corrected). - i-one

    Usually through the for xml path and done:
    If following the example on SQLFiddle, then this:

     select rhp.RequestHistoryId, STUFF(( SELECT ', ' + [LastName] FROM performers _p join RequestHistoryPerformers r on r.PerformerId=_p.Id where r.RequestHistoryId=rhp.RequestHistoryId FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') PerformersNames from RequestHistoryPerformers rhp group by rhp.RequestHistoryId

    https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server