title: apply sorting in stuff, stuff distinct and order by
body: <p>I need to combine rows with same value into one using STUFF operator. I archived the rows concatenation, but sequence of values is not correct. </p>
  
  <p><a href="http://www.sqlfiddle.com/#!18/2f420/1/0" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!18/2f420/1/0</a></p>
  
  <p>as you see on result window, TypeB is next to TypeA, but sequence of Amount values is not corresponding the Type values, where 0.09K should be next to 3k</p>
  
  <p><a href="https://i.stack.imgur.com/lFbgx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lFbgx.png" alt="enter image description here"></a></p>
  
  <p>How to make STUFF of distinct values and save the sequence (order by rn) ?</p>
  
  <pre><code>create table SomeTable (Code int, Type nvarchar(50), Amount nvarchar(50), Date datetime);
  
  insert into SomeTable VALUES(20, 'TypeA', '12k', cast('01/01/2019' as datetime));
  insert into SomeTable VALUES(20, 'TypeA', '11k', cast('01/01/2018' as datetime));
  insert into SomeTable VALUES(22, 'TypeA', '17k', cast('01/02/2017' as datetime));
  insert into SomeTable VALUES(22, 'TypeA', '17k', cast('01/01/2017' as datetime));
  insert into SomeTable VALUES(25, 'TypeB', '0.09k', cast('01/02/2019' as datetime));
  insert into SomeTable VALUES(25, 'TypeA', '3k', cast('01/01/2019' as datetime));
  
  with t as (
  
    select 
        row_number() over(partition by st.Code order by st.Date) as rn,
        st.Code, 
        st.Type, 
        st.Amount, 
        st.Date 
    from SomeTable st
  )
  
  select 
    t1.Code,
    stuff((select distinct ',' + t.Type from t 
           where t.Code = t1.Code
           for XML path('')), 1,1, '') as Type,
    stuff((select distinct ',' + t.Amount from t 
           where t.Code = t1.Code
           for XML path('')), 1,1, '') as Amount,
    t1.Date
  from t as t1
  where t1.rn = 1
  order by t1.Date
  </code></pre>