Combine multiple rows into single row in SQL View (without group by or with CTE?)

Question

I'm trying to create complex view, when I have relation one to many then I want to put these value into single row.

Staff (main table)

ID            Name
1             aaa
2             bbb

OtherStaff (table one to many)

ID            StaffId       Name       Value
1             1             xxx        888
2             1             yyy        777
3             2             vvv        333

SomeTable (table one to one)

 Id          StaffId        SomeVal
 1           1              qwert
 2           2              asd

Result:

ID            Name          OtherStaff                SomeVal
1             aaa           xxx, 888; yyy, 777        qwert
2             bbb           vvv, 333                  asd

View:

CREATE VIEW MyView
AS 
SELECT DISTINCT
    Staff.Id,
    MagicCombine(OtherStaff.Name, OtherStaff.Value) -- pseudocode
    SomeTable.SomeVal   
FROM 
    dbo.[Staff] Staff 
    JOIN dbo.[OtherStaff] OtherStaff ON OtherStaff.StaffId = Staff.Id
    JOIN dbo.[SomeTable] SomeTable ON SomeTable.StaffId= Staff.Id

I have read that I can use GroupBy but in fact I will have a lot of JOINS and Columns, but GroupBy requires to put them all into clause. I was thinking about more elegant solution? Can CTE – Common Table Expressions be useful somehow?

Answer 1

Your "magic function" would appear to be STRING_AGG().

The code would then look like this:

CREATE VIEW MyView AS 
    SELECT Staff.Id,
           STRING_AGG(CONCAT(OtherStaff.Name, ', ', OtherStaff.Value), '; ')  WITHIN GROUP (ORDER BY OtherStaff.Name),
           SomeTable.SomeVal   
    FROM dbo.[Staff] Staff JOIN
         dbo.[OtherStaff] OtherStaff
         ON OtherStaff.StaffId = Staff.Id JOIN
         dbo.[SomeTable] SomeTable
         ON SomeTable.StaffId = Staff.Id
    GROUP BY Staff.Id, SomeTable.SomeVal;

Listing unaggregated columns in both the GROUP BY and SELECT should not be too troublesome. After all, you can just cut-and-paste them.