apply sorting in stuff, stuff distinct and order by

user_7060504 0
Viewed 6111

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.

http://www.sqlfiddle.com/#!18/2f420/1/0

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

enter image description here

How to make STUFF of distinct values and save the sequence (order by rn) ?

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
sql-server-2008 tsql sql-server sql
3 Answers

Apply sorting to any result in SQL is done by using the order by clause.
It's not the stuff function that's giving you a hard time, it's the fact that you didn't specify an order by to the subqueries.
Without and order by clause, the subqueries returns the records in an arbitrary order - and this is why you get the result you are getting now.
Please note, however, that since the order of the results is arbitrary, you might get a different result next time you run the query.

Therefor, you must specify an order by clause in your subqueries that generates the C.S.V columns.

Now I'm not really sure what order are you expecting, but it's probably either sorting by rn or by rn desc (based on the image I think it's the first).
However, there is a trick here - since you want distinct values of type and amount, you can't simply use rn in the order by clause - SQL Server will raise the following error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

So, the trick is to not use distinct, but instead, use group by, and not use rn in the order by clause, but max(rn). This way, you get TypeA,TypeB and 3k,0.09k - and it will be consistent.

Having said all that - here's a revised version of your code (the cte remains the same):

select  t1.Code,
  stuff((
            select ','+ t.Type
            from t 
            where t.Code = t1.Code
            group by t.Type
            order by max(rn) 
            for XML path('')
        ), 1,1, '') as Type,
  stuff((select ',' + t.Amount 
         from t 
         where t.Code = t1.Code
         group by  t.Amount 
         order by max(rn) 
         for XML path('')), 1,1, '') as Amount,
  t1.Date
from t as t1
where t1.rn = 1
order by t1.Date

And the results:

Code    Type            Amount      Date
22      TypeA           17k         2017-01-01
20      TypeA           11k,12k     2018-01-01
25      TypeA,TypeB     3k,0.09k    2019-01-01

NOTE Salman's comment to the question (which I've only seen now) has a very valid point - distinct might not be a good option here at all.
In a scenario where you had TypeA, TypeB, TypeA with corresponding amounts 10K, 11K, 12K -
If you do distinct, the result would be TypeA, TypeB and amounts 10K, 11K, 12K - and it would be impossible to tell which amount belongs to which type.

user_30945330

Put order by into stuff segment:

 select 
  t1.Code,
  stuff((select distinct top 100 percent  ',' + t.Type from t 
         where t.Code = t1.Code order by ',' + t.Type 
         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
user_31799570

How about trying like this?

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
),
t2 as (
  select distinct top 100 percent
      st.code,
      st.type,
      st.amount
  from SomeTable st
  order by st.code, st.type, st.amount
)

select 
  t1.Code,
  stuff((select distinct ',' + t2.Type from t2 
         where t2.Code = t1.Code
         for XML path('')), 1,1, '') as Type,
  stuff((select ',' + t2.Amount from t2 
         where t2.Code = t1.Code
         for XML path('')), 1,1, '') as Amount,
  t1.Date
from t as t1
where t1.rn = 1
order by t1.Date
user_105272770
Related