SQL Azure – Group child records by parent in order

Lets say we have two tables

A site parent table T001 consisting of two fields

And a planning application child table T002 consisting of three fields

We have the following records in the parent table T001
1 – Site01Green
2 – Site02Red
3 – Site03Blue

And the following related children in the planning application table T002;
1 – 1 – 2019/098
1 – 1 – 2018/032
1 – 2 – 2017/987
1 – 3 – 2015/100
1 – 3 – 2014/456
1 – 3 – 2014/657

And we would like to write a query that would create a list combining the two tables showing all the planning applications for each site listed in order.

This is the list we are aiming at
1 – 2018/032,2019/098
2 – 2017/987
3 – 2014/456,2014/657,2015/100

Using SQL Server 2017 or later (including SQL Azure) the following will work

CREATE VIEW [dbo].[View01Sitesandtheirplanapps] AS SELECT PKIDT001, STRING_AGG(PlanRef,',') WITHIN GROUP (ORDER by PlanRef ASC) as PlanRefs
From dbo.T002

This can then be combined in a separate view that will include the sitename should it be required – this is a tremendous example the structure of which I will be using often.

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, SQL Azure, SQL Server. Bookmark the permalink.