Concatenate with ORDER PRESERVATION

Question

I have a table

tblSomeData

    OrderOccurance     ID   Data
    --------------     --   ----
    1                  1    HTMedia
    2                  1    Hedge
    3                  1    Bowing
    4                  1    FonWirelessLtd  

The first column "OrderOccurance" indicates in which order the elements i.e. Data will appear in the final output

The Expected Output will be

ID  Data
--  -----
1   HTMedia,Hedge,Bowing,FonWirelessLtd

I have done the below program

Select 
    ID
    , Data = stuff((Select ',' + Cast(Data As Varchar(20)) From tblSomeData t2 
          Where t2.ID = t1.ID for xml path('')),1,1,'')
From tblSomeData t1
Group by t1.ID

I am getting the output as

ID  Data
--  -----
1   Bowing,FonWirelessLtd,Hedge,HTMedia

It seems that, the FOR XML PATH() sorts by itself and then performs the concatenation.

How can I bring the expected output.

I am using SQL Server 2005.

Thanks

Answer 1

If you want to order, you need to tell SQL Server what column to order by - in your current query, you don't have any ORDER BY clause. Just add that and you should be fine:

SELECT
    ID, 
    Data = STUFF(
              (SELECT ',' + CAST(Data As VARCHAR(20)) 
               FROM dbo.tblSomeData t2 
               WHERE t2.ID = t1.ID 
               ORDER BY OrderOccurance
               FOR XML PATH('')), 1, 1, '')
FROM 
   dbo.tblSomeData t1
GROUP BY
   t1.ID