0

I'm new to this page and this is the first time i post a question. Sorry for anything wrong. The question may be old, but i just can't find any answer for SQL AnyWhere.
I have a table like 

Order |  Mark  
======|========   
1     | AA  
2     | BB  
1     | CC  
2     | DD  
1     | EE

I want to have result as following 

Order | Mark  
1     | AA,CC,EE  
2     | BB,DD

My current SQL is 

Select Order, Cast(Mark as NVARCHAR(20))  
From #Order  
Group by Order

and it just give me with result completely the same with the original table.

Any idea for this?

Seki
  • 11,135
  • 7
  • 46
  • 70
ductoyo
  • 3
  • 2

1 Answers1

0

You can use the ASA LIST() aggregate function (untested, you might need to enclose the order column name into quotes as it is also a reserved name):

SELECT Order, LIST( Mark )
FROM #Order
GROUP BY Order;

You can customize the separator character and order if you need.

Note: it is rather a bad idea to

  • name your table and column name with like regular SQL clause (Order by)
  • use the same name for column an table (Order)
Seki
  • 11,135
  • 7
  • 46
  • 70
  • it worked perfectly, thank you so much. Also thanks for the column name advice & editting my post for better look. – ductoyo Feb 12 '17 at 21:31
  • You're welcome. If it addressed your issue, you should [accept the answer](http://meta.stackoverflow.com/a/65088/173356)... – Seki Feb 13 '17 at 12:50