-2

Suppose that I have a table named order_detail:

enter image description here

As you can see that the data is not duplicate but they might be some error from data source about spacing values in the column Product.

My goal is to make the table like this:

enter image description here

How can I write the query to solve this problem on SQL Server? Thank you!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Sunkung Krp
  • 13
  • 1
  • Does this answer your question? [Concat single column fields using GROUP BY](https://stackoverflow.com/questions/24199258/concat-single-column-fields-using-group-by) – Jonathan Lam Aug 30 '22 at 03:50
  • @JonathanLam I'm not sure but I had tried and it gave me an error T___T – Sunkung Krp Aug 30 '22 at 04:38
  • Just see that you're using the SQL server but not hive, then it shouldn't have `concat_ws()` function. You should use this one: https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – Jonathan Lam Aug 30 '22 at 06:23
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Aug 30 '22 at 07:15
  • You can also use STRING_AGG() and a GROUP BY. – Srinivasan Rajasekaran Aug 30 '22 at 08:48

1 Answers1

1

try following query you get exact output, i have change field name 'Price Unit' to 'Price_Unit' in my query so please take care of that

Select ID ,Name,Surname,
Stuff((SELECT distinct ' ' + Cast(Product as varchar(10))
   FROM order_detail t2
   Where t1.ID = t2.ID
   FOR XML PATH('')),1,1,'') Product,
Price_Unit,Date
From order_detail t1
Group By ID ,Name,Surname,Price_Unit,Date
Uday Dodiya
  • 339
  • 2
  • 15