STUFF XML PATH + Partition By

Question

STUFF XML PATH + Partition By

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

JeremyU SSChasing Mays Points: 637 More actions · Answer 1

@jonathan-2 AC Roberts or anyone, do you know if there is a chance to ORDER BY in alphabetical order whatever is a column? Right now they are in a very random order.

This reply was modified 1 year, 1 month ago by JeremyU.
This reply was modified 1 year, 1 month ago by JeremyU.

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 2

JeremyU wrote:

@Jonathan AC Roberts or anyone, do you know if there is a chance to ORDER BY in alphabetical order whatever is a column? Right now they are in a very random order.

Not sure what you want. Can you show the output now and the output you want?

Also, can you include a table create and insert statement to provide consumable data.

JeremyU SSChasing Mays Points: 637 More actions · Answer 3

Jonathan thank you for help. It was possible to insert ORDER BY in STUFF function but before XML part. I initially didn't understand it. Thanks for your willingness to help.

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 4

JeremyU wrote:

@Jonathan AC Roberts or anyone, do you know if there is a chance to ORDER BY in alphabetical order whatever is a column? Right now they are in a very random order.

I think I've worked out what you want. You can put an ORDER BY in the select in the stuff to order by the values in the column:

SELECT a.DC, 
       a.UPC, 
       MIN(a.[MIN START POG DATE]) OVER(PARTITION BY a.DC, a.UPC) AS 'MIN START POG DATE', 
       MAX(a.[MAX START POG DATE]) OVER(PARTITION BY a.DC, a.UPC) AS 'MAX START POG DATE', 
       STUFF((SELECT ', ' + b.MASTERCHAINNAME
               FROM #pre_filtering   b
              WHERE b.DC  = a.DC
                AND b.UPC = a.UPC
              ORDER BY b.MASTERCHAINNAME
                FOR xml PATH('')
              ), 1, 2, '') AS MASTERCHAINNAME  -- doesn't understand that some UPCs + DCs are not connected to MASTERCHAINNAMES
  FROM #pre_filtering a
 GROUP BY DC, UPC, [MIN START POG DATE], [MAX START POG DATE]
 ORDER BY DC, UPC