-1

I have an example:

enter image description here

I want it to look like this:

enter image description here

Pretty much, there will be null cells following a value in the Amount Column. I want to concatenate the comment cells of those rows with null cells. I'm not sure how to do this. Is there a way to do this?

Dat Nguyen
  • 163
  • 4
  • 11
  • what is your sql server version – Fahmi Aug 05 '20 at 04:33
  • Its microsoft SQL Server Studio 18. I tried group_concat or string_concat but those are not recognizable – Dat Nguyen Aug 05 '20 at 04:34
  • Check out this question, it might help you! https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv – Bryan Aug 05 '20 at 04:35
  • I tried this method with cross apply using XML path as well, but the problem is sometimes the ID would be the same, so it's just hard to do this. Sorry the original question was off, I edited it – Dat Nguyen Aug 05 '20 at 04:38
  • 1
    A bit concerning that there is no explicit order in the rows. You shouldn't rely on the implicit order of rows in the results - that may change. – Sean Aug 05 '20 at 05:53
  • @DatNguyen . . . SQL tables represent *unordered* sets. You code has no ordering column but the result set seems to depend on ordering. Hence, you cannot do what you want. – Gordon Linoff Aug 05 '20 at 12:07

3 Answers3

2

If there will be null cells following a value in the Amount Column (as you told in question) you can use ROW_NUMBER() to get unique number in cte and then calculating sum (order by new unique numbers) for every row (will be the same for one Amount). And at last, using cte in FOR XML PATH().

--getting Sum as unique for every sequence (Amount and following values)
with cte AS
(

select Id,Amount,Number,sum(Amount)over(order by Number rows unbounded preceding)SumSeq,comment
from
 (
   select Id,Amount,comment,
   row_number()over(order by Id)Number from Table
 )x

)

 select id,max(Amount),ConcatComments from
  (
    select id,SumSeq,Amount, 
    stuff((select Concat(',',comment) 
    from cte cte1 where cte1.SumSeq=cte2.SumSeq 
    for xml path('')),1,2,'') ConcatComments from cte cte2
 )Z
 group by SumSeq,id,ConcatComments
Olga Romantsova
  • 1,096
  • 1
  • 6
  • 8
0

You can use the STRING_AGG function to concatenate the comments. Something like this:

Select ID, MAX(Amount) as Amount, STRING_AGG(Comment, ', ') as Comment
From [YourTable]
Group By ID;
dashingdove
  • 304
  • 1
  • 4
  • 15
  • Yes, I tried STRING_AGG as well but for some reasons, the SQL Server Studio 18 could not recognize it – Dat Nguyen Aug 05 '20 at 04:36
  • @DatNguyen There is nothing now that links the comments to the amount. I would not store your data like that if at all possible. For STRING_AGG, you may need to check your database's compatibility level : https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15 – dashingdove Aug 05 '20 at 04:52
  • I agree that this database is really bad, but it is a pre-existed one. I've been trying to actually implement pattern using a loop but not sure how to do it so far – Dat Nguyen Aug 05 '20 at 04:55
  • You could loop through each row programatically with a cursor if you need to (https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/). – dashingdove Aug 05 '20 at 05:03
  • Thanks. Let me take a look over there – Dat Nguyen Aug 05 '20 at 05:06
0

Your data has no column specifying the ordering, but the results seem to depend on ordering. If you do have such a column, you can assign a grouping by counting the non-NULL values of amount for each id, and then aggregate. That would be:

select id, grp,
       string_agg(comment, ', ') within group (order by <ordering column>) as comments
from (select t.*, count(amount) over (partition by id order by <ordering column>) as grp
      from t
     ) t
group by id, grp;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786