SQL: Create flattened string from hierarchical (one-to-many) data

Question

I have been working on getting some data into the format as shown below. I have finally done it but I am curious to know in what way it could be improved. My steps to the final solution are included. The correct result is produced by the final query. Format: Location1:Date1:Code1,Code2,Code...:Date2:Code1,Code2,Code...:Date...:Code...//Location2:Date...:Code...//Location...:Date...:Code...

Result: Loc1:2016-01-01:Alpha:2016-01-03:Bravo,Charlie,Charlie:2016-01-04:Alpha,Alpha:2016-01-05:Alpha,Delta,Echo//Loc2:2016-01-01:Bravo,Delta//Loc3:2016-01-02:Bravo

declare @Operation table (
ID int identity not NULL,
ItemID int,
LocationID int,
[Date] date
)

declare @Location table (
ID int identity not NULL,
Name varchar(200)
)

declare @LineItem table (
ID int identity not NULL,
OperationID int,
CodeID int
)

declare @Code table (
ID int identity not NULL,
Value varchar(200)
)

insert @Operation
values
(1, 1, '2016-01-01'),
(1, 2, '2016-01-01'),
(1, 1, '2016-01-03'),
(1, 1, '2016-01-04'),
(1, 3, '2016-01-02'),
(1, 1, '2016-01-05')

insert @Location
values
('Loc1'),
('Loc2'),
('Loc3')

insert @LineItem
values
(1, 1),
(2, 4),
(2, 2),
(3, 3),
(3, 2),
(3, 3),
(4, 1),
(4, 1),
(5, 2),
(6, 5),
(6, 4),
(6, 1)

insert @Code
values
('Alpha'),
('Bravo'),
('Charlie'),
('Delta'),
('Echo')

select
*
from @Operation vo
join @Location vl
on vl.ID = vo.LocationID
join @LineItem vli
on vli.OperationID = vo.ID
join @Code vc
on vc.ID = vli.CodeID

select
vl.Name as OpLocation,
vo.[Date] as OpDate,
OpCodes
from @Operation vo
join @Location vl
on vl.ID = vo.LocationID
cross apply (
select
    stuff(List, 1, 1, '') as OpCodes
from (
    select
        ',' + vc.Value
    from @LineItem vli
    join @Code vc
        on vc.ID = vli.CodeID
    where vli.OperationID = vo.ID
    order by vc.Value
    for xml path('')
    ) as X(List)
) as xCodes

select
vl.Name as OpLocation,
vo.[Date] as OpDate,
OpCodes
from @Operation vo
join @Location vl
on vl.ID = vo.LocationID
cross apply (
select
    stuff(List, 1, 1, '') as OpCodes
from (
    select
        ',' + vc.Value
    from @LineItem vli
    join @Code vc
        on vc.ID = vli.CodeID
    where vli.OperationID = vo.ID
    order by vc.Value
    for xml path('')
    ) as X(List)
) as xCodes
group by vl.Name, vo.[Date], OpCodes

select
vl.Name as OpLocation,
OpDateAndCodes
from @Operation vo
join @Location vl
on vl.ID = vo.LocationID
cross apply (
select
    convert(varchar(10), [Date], 21) + ':' + OpCodes as OpDateAndCodes
from (
    select
        stuff(List, 1, 1, '') as OpCodes
    from (
        select
            ',' + vc.Value
        from @LineItem vli
        join @Code vc
            on vc.ID = vli.CodeID
        where vli.OperationID = vo.ID
        order by vc.Value
        for xml path('')
        ) as X(List)
    ) as Codes
) as xODC
group by vl.Name, OpDateAndCodes

;with cte as (
select
    vl.Name as Location,
    OpDateAndCodes
from @Operation vo
join @Location vl
    on vl.ID = vo.LocationID
cross apply (
    select
        convert(varchar(10), [Date], 21) + ':' + OpCodes as OpDateAndCodes
    from (
        select
            stuff(List, 1, 1, '') as OpCodes
        from (
            select
                ',' + vc.Value
            from @LineItem vli
            join @Code vc
                on vc.ID = vli.CodeID
            where vli.OperationID = vo.ID
            order by vc.Value
            for xml path('')
            ) as X(List)
        ) as Codes
    ) as xODC
)
select
    Location,
    stuff(List, 1, 1, '') as ODC
from cte cte1
cross apply (
    select
        ':' + OpDateAndCodes
    from cte cte2
    where cte2.Location = cte1.Location
    for xml path('')
    ) as X(List)

//SOLUTION
;with cte as (
select
    vl.Name as Location,
    OpDateAndCodes
from @Operation vo
join @Location vl
    on vl.ID = vo.LocationID
cross apply (
    select
        convert(varchar(10), [Date], 21) + ':' + OpCodes as OpDateAndCodes
    from (
        select
            stuff(List, 1, 1, '') as OpCodes
        from (
            select
                ',' + vc.Value
            from @LineItem vli
            join @Code vc
                on vc.ID = vli.CodeID
            where vli.OperationID = vo.ID
            order by vc.Value
            for xml path('')
            ) as X(List)
        ) as Codes
    ) as xODC
)
select
    distinct
        stuff(List, 1, 2, '')
from cte cte1
cross apply (
    select
        '//' + OpLocationDateAndCodes
    from (
        select
            distinct
                Location + ':' + stuff(List, 1, 1, '') as OpLocationDateAndCodes
        from cte cte2
        cross apply (
            select
                ':' + OpDateAndCodes
            from cte cte3
            where cte3.Location = cte2.Location
            for xml path('')
            ) as X(List)
        ) as OLDC
    for xml path('')
    ) as X(List)

Answer 1

Here's an approach I came up with that uses a single query with sub-queries. The output matches your code exactly.

SELECT STUFF((
    SELECT
        '//' + L.Name
        + (
            SELECT ':' + CONVERT(VARCHAR(50), O.Date) + ':'
                + STUFF((
                    SELECT ',' + C.Value
                    FROM @LineItem LI
                        INNER JOIN @Code C
                            ON C.ID = LI.CodeID
                    WHERE LI.OperationID = O.ID
                    FOR XML PATH('')), 1, 1, '')
            FROM @Operation O
            WHERE O.LocationID = L.ID
            FOR XML PATH('')
        )
    FROM @Location L
    FOR XML PATH('')
), 1, 2, '')