Trying to order by the [Booking Date]. The output looks like 1111 | Adelaide; Brisbane | 4000; 5000 but the postcode for Adelaide = 5000 and Brisbane = 4000. This will cause some confusion for the users. The error im getting is ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
IF OBJECT_ID('tempdb..#locations') IS NOT NULL DROP TABLE #locations
select distinct([Case ID]), [Booking City], [Booking Postcode], cast([Booking Date] as date) 'Booking Date'
into #qld_locations
from ETL_Operations
where cast([Booking Date] as date) between '2019-07-01' and '2019-09-30' and Service like 'Foundation'
SELECT DISTINCT
[Case ID], -- primary key
[Service] =
STUFF((SELECT DISTINCT '; ' + [Booking City]
FROM #qld_locations b -- input table
WHERE (b.[Case ID] = a.[Case ID]) -- primary key
order by [Booking Date]
FOR XML PATH('')), 1, 2, ''),
[Topic] =
STUFF((SELECT DISTINCT '; ' + [Booking Postcode]
FROM #qld_locations b -- input table
WHERE (b.[Case ID] = a.[Case ID]) -- primary key
order by [Booking Date]
FOR XML PATH('')), 1, 2, '')
FROM #locations a -- input table
GROUP BY [Case ID] -- primary key