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

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

CodePudding user response：

Your stuff needs meta data else the output will be formatted as xml. Also, if you are using version 2016 you can use the STUFF() function to achieve the same functionality. I added a normalization CTE to add the calculated P1,P2..PX values based on IMAGEORDER.

;WITH Normalized AS
(
    SELECT      
        '&P' CAST(ROW_NUMBER() OVER (ORDER BY IMAGEORDER) AS NVARCHAR(10)) '=' FILENAME AS Item,
        IMAGEORDER
    FROM
        MYTABLE
    WHERE
        IMAGEORDER<>0
)


SELECT STUFF((SELECT Item
            FROM Normalized ORDER BY IMAGEORDER             
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,1,'') AS Txt

CodePudding user response：

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