SQL Server: Select multiple records in one select statement

Question

In a query like this one:

SELECT * 
FROM `Order`
WHERE `CustID` = '1'

My results are displayed like so:

| CustID| Order |
-----------------
| 1     | Order1|
| 1     | Order2|
| 1     | Order3|
-----------------

How do I write SQL statement, to get a result like this one?:

| CustID| Order                 |
---------------------------------
| 1     | Order1, Order2, Order3|
---------------------------------

In mySQL it's possible with Group_Concat, but in SQL Server it gives error like syntax error or some.

possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Giorgos Betsos, May 25 '15 at 12:30
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — wiretext, May 25 '15 at 12:43
Check this answer http://stackoverflow.com/a/27341271/1080354 — gotqn, May 25 '15 at 12:45

artm · Accepted Answer · 2015-05-25T13:08:15.940

6

Use xml path (see fiddle)

SELECT distinct custid, STUFF((SELECT ',' +[order]
FROM table1 where custid = t.custid
FOR XML PATH('')), 1, 1, '')
FROM table1 t
where t.custid = 1

STUFF replaces the first , with an empty string, i.e. removes it. You need a distinct otherwise it'll have a match for all orders since the where is on custid.

FOR XML
PATH Mode
STUFF

edited May 25 '15 at 13:08

answered May 25 '15 at 12:44

artm

8,554
3
26
43

I see we thought about the same thing... :-) – Zohar Peled May 25 '15 at 12:48
@ZoharPeled 1 minute too late :) Great minds think alike so have a 1+ :) – artm May 25 '15 at 12:52
Just edit your links to make it more readable and you'll have a great answer. still got my upvote :-) – Zohar Peled May 25 '15 at 12:53
@ZoharPeled How? Put a newline between them but yeah doesn't look right. Can you edit? – artm May 25 '15 at 12:54
click on the edited link and see... Note that for a line break you need 2 spaces before the actual line break – Zohar Peled May 25 '15 at 12:56

score 4 · Answer 2 · edited May 23 '17 at 12:30

You can use Stuff function and For xml clause like this:

 SELECT DISTINCT CustId, STUFF((
   SELECT ','+ [Order]
   FROM [Order] T2
   WHERE T2.CustId = T1.CustId
   FOR XML PATH('')
 ), 1, 1, '')
 FROM  [Order] T1

fiddle here

Note: Using order as a table name or a column name is a very, very bad idea. There is a reason why they called reserved words reserved.
See this link for my favorite way to avoid such things.

Onur Cete · Answer 3 · 2015-05-25T12:43:01.683

-1

try this. Change table name and column names for what you need;

SELECT custID, 
       LISTAGG(Order, ', ') WITHIN GROUP (ORDER BY Order) text
  FROM table_name
 GROUP BY custID

edit for MSSQL . You should use group_concat function.

SELECT custID, GROUP_CONCAT(Order)
  FROM table_name
 WHERE CustID = 1
 GROUP BY custID;

edited May 25 '15 at 12:43

answered May 25 '15 at 12:37

Onur Cete

263
1
2
10

It is about `SQL Server`, there is no such function as `LISTAGG` there. – gotqn May 25 '15 at 12:38
1

Again, there is no ` group_concat` function in `Microsoft SQL Server` :-) – gotqn May 25 '15 at 12:46
sql server does not have group_concat either. – Zohar Peled May 25 '15 at 12:46

SQL Server: Select multiple records in one select statement

3 Answers3

Linked