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
Barbara
Chloe Barbara
Dani Barbara
Ellie Barbara
Fiona

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,


SELECT people.name AS Name,
people.mother AS Mother,
(SELECT people_inner_selfjoin.name+','
FROM people
LEFT OUTER JOIN people people_inner_selfjoin
ON people_inner.name= people_inner_selfjoin.mother
WHERE people.name = people.mother
FOR XML PATH('')
) 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s