SQL Select FOR XML PATH - Combine ordering and concatenate string with increasing counter

Question

I am trying to get from the following SQL table

CODE , FILENAME     , ORDER
xxxx , Noimage.jpg  , 0 
xxxx , yyyy.jpg     , 2
xxxx , zzzz.jpg     , 1
xxxx , aaaa.jpg     , 3

the following result --> p1=zzzz.jpg&p2=yyyy.jpg&p3=aaaa.jpg

I have tried

SELECT STUFF((SELECT '&' + FILENAME
            FROM MYTABLE
            WHERE IMAGEORDER<>0
            FOR XML PATH('')) ,1,1,'') AS Txt 

but i cant figure out how can i combine the ordering and based on ordering to contacatenate the p1 , p2 ,p3

Any help is appreciated

Answer 1

You just need to order the rows:

DECLARE @t TABLE (Code VARCHAR(255), Filename VARCHAR(255), ImageOrder INT)

INSERT INTO @t VALUES
('xxxx', 'Noimage.jpg', 0),
('xxxx', 'yyyy.jpg', 2),
('xxxx', 'zzzz.jpg', 1),
('xxxx', 'aaaa.jpg', 3)
 
SELECT REPLACE(
  (SELECT CAST('&p' + CAST(ImageOrder AS varchar) + '=' + Filename AS VARCHAR(MAX))
  FROM @t
  WHERE ImageOrder <> 0
  ORDER BY ImageOrder
  FOR XML PATH (''))
  , 'amp;', '') AS Txt

Which will give you the result:

&p1=zzzz.jpg&p2=yyyy.jpg&p3=aaaa.jpg