How to group multiple rows of multiple columns in a joint table with grouping by 1 column

Question

I have a joint table from 3 different tables like this.

Select DoctorTable.Doctor, NurseTable.Nurse , PatientTable.Patents 

FROM PatientTable

LEFT JOIN DoctorTable on DoctorTable.DoctorCode = PatientTable.DoctorCode

LEFT JOIN NurseTable on DoctorTable.DoctorCode = NurseTable.DoctorCode


Patient     Doctor        Nurse
John        Peter         Mary
John        Peter         Amy
Kate        Terry         Mary
Kate        Leo           Ivy
Paul        NULL          NULL
Emily       NULL          NULL

And I want to join the rows which has the same value with a comma in 'Doctor' Column to have the result below

Patient     Doctor        Nurse
John        Peter         Mary, Amy
Kate        Terry, Leo    Mary, Ivy
Paul        Null          Null
Emily       NULL          NULL

As I still have some columns to join with this table and also need to group the rows with ',', using FOR XML PATH('') to group each column will be very slow. So how can I group this table efficiently?

Answer 1

I think the answer is using an aggregation function which concatenates rows in the group. Too bad TSQL does not have one (how hard is that?), but if you don't mind using SQLCLR, here is how to create a user defined aggregate concatenation function with .NET in MSDN:

MSDN String Utility Functions Sample

<Serializable(), Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Microsoft.SqlServer.Server.Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
Public Class Concatenate : Implements Microsoft.SqlServer.Server.IBinarySerialize

    Private Builder As StringBuilder

    Public Sub Init()
        Builder = New StringBuilder
    End Sub

    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then Return
        Builder.Append(",").Append(value.Value)
    End Sub

    Public Sub Merge(ByVal other As Concatenate)
        Builder.Append(other.Builder)
    End Sub

    Public Function Terminate() As SqlString
        If Builder Is Nothing Then Return New SqlString("")
        Return New SqlString(Builder.ToString.Substring(1))
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
        If r Is Nothing Then Throw New ArgumentNullException("r")
        Builder = New StringBuilder(r.ReadString())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
        If w Is Nothing Then Throw New ArgumentNullException("w")
        w.Write(Builder.ToString())
    End Sub

End Class

Then, you can do something like:

SELECT PatientCode, Patient, dbo.Concatenate(Doctor), dbo.Concatenate(Nurse) 
FROM PatientTable
LEFT JOIN DoctorTable on DoctorTable.DoctorCode = PatientTable.DoctorCode
LEFT JOIN NurseTable on DoctorTable.DoctorCode = NurseTable.DoctorCode
GROUP BY PatientCode, Patient