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
( select TOP 100 PERCENT
pic, pisc, piin, piisn, piidta
order by pic, pisc, piin, piisn) B
Group By B.PIC, B.pisc, B.piin
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