How do I get SQL query with join and using STUFF and FOR XML PATH be sorted correctly

Question

I have a master table that has one row per key and a detail table that has many rows per key with a sequence field that has a description field I need concatenated together and create one row per key. My code does this fine but the detail data row does not have the concatenated data in the correct order. The data is delivered to me in an Excel spreadsheet and I use the Import Wizard to add the data to the database. Since the order is not correct in the detail data I added a sub select to sort the data by key and sequence number that is the input into the STUFF WITH XML PATH. I am still getting the data in an incorrect order. If I sort the data in the spreadsheet first and then load it to the database it works fine. I really need this to work dynamically as I want to distribute this to my team and we can use it for different tables. Any ideas on why the sub select with the STUFF FOR XML PATH is not working? How can I do what I need?

Here is the code I have:

SELECT pic, pisc, piin
    , STUFF((SELECT ' ' + P.PIIDTA FROM PI115AP P
    Where P.PIC =B.PIC
    and P.PISC = B.PISC
    and P.PIIN = B.PIIN FOR XML PATH(''), type
    ).value('.', 'nvarchar(max)'),1,1,'') As CombinedDetail
    From 
    ( select TOP 100 PERCENT
        pic, pisc, piin, piisn, piidta
     from PI115AP
    order by pic, pisc, piin, piisn) B
    Group By B.PIC, B.pisc, B.piin

Thank you!

Answer 1

You have a lot of pieces and parts out of place for this to work the way you want it to. Your FOR XML is not in the order you want because the subquery has no order by. The actual result set is not in the order you want either because the main query does not have an order by. I don't really understand the point of the B subquery. The top does NOT order the actual results when using top, it just defines which rows to retrieve.

Pretty sure you want something more like this.

SELECT pic
    , pisc
    , piin
    , STUFF((SELECT ' ' + P.PIIDTA 
            FROM PI115AP P
            Where P.PIC = B.PIC
                and P.PISC = B.PISC
                and P.PIIN = B.PIIN 
            order by p.pic
                , p.pisc
                , p.piin
                , p.piisn
            FOR XML PATH(''), type
    ).value('.', 'nvarchar(max)'),1,1,'') As CombinedDetail
From PI115AP B
Group By B.PIC
    , B.pisc
    , B.piin
order by b.pic
    , b.pisc
    , b.piin