Posted in Software Development

Concatenate values from multiple rows with self join on SQL Server

Due to some inheritance limitations when using LinqSQL I have recently needed to do something quite strange in a SQL View which made more sense in Linq. However I never say no to a SQL challange and a few horus later a rather beautiful (in my opinion) solution was finished.

First lets look at some example data for my problem. The following data is a table off ladies attending a Mother – Daughter Event

Name Mother
Anna Fiona
Chloe Barbara
Dani Barbara
Ellie Barbara

The view we would like to get is something like this

Name Mother Daughters
Anna Fiona
Barbara  Chloe,Dani,Ellie,
Chloe Barbara
Dani Barbara
Ellie Barbara
Fiona  Anna,

people.mother AS Mother,
FROM people
LEFT OUTER JOIN people people_inner_selfjoin
ON people_inner_selfjoin.mother
WHERE = people.mother
) AS Daughters
FROM people

*You could add a substring to get rid of the final , but in my case this wasn’t required with the C# string.Split method

I found the best way to understand was to give it a go with some data and provided you understand the way joins work you can manipulate it to suit your needs.

Naturally the above is just demo data etc. in my actual implementation I also needed to include a case statement as the second column was also populated, if not with a parent then by itself, so the data had to be cleaned up even more.

I learnt something doing this hope it helps.