SQL - column from all rows returned from a Join into a single column?

Question

I'm writing an SQL query that displays a deliveries details, plus in a seperate column, all the delivery item ID's seperated by commas. Both Deliveries and DeliveryItems are in a seperate table, so I guess I'll be using a Join to retrieve all items that link to that delivery, but how do I get them into a column I'll be displaying, and seperate each item with a comma?

Thanks

EDIT: Here's an idea of my table structure

Deliveries -
ID (PK)
...(other non relevant fields)...

DeliveryItems -
ID (PK)
Delivery (links to ID of delivery)

So for every item of that delivery, I want to display the item ID comma seperated in a column for that delivery.

post your table structure and what result exactly you want ? — Jeevan Bhatt, Dec 08 '10 at 10:24
Duplicate question, already answered several times as pointed out by TomaszK. Should be closed. — Pavel Urbančík, Dec 08 '10 at 13:19

score 2 · Answer 1 · answered Dec 08 '10 at 10:38

you can do it by using PIVOT in SQL server 2005. PIVOT is used for Transpose row to column or column to row, so in your case you can get data in row format by using simple join after that you can use PIVOT for to transpose data and then you can concatenate columns in a single column.

check this link, it has a good example.

score 0 · Answer 2 · answered Dec 08 '10 at 10:21

0

There is a GROUP_CONCAT function in MySQL that does just that (take a look at the documentation). Look for something similar in your DBMS.

answered Dec 08 '10 at 10:21

TomaszK

435
2
4

In SQL Server he will likely have to write his own function. – TToni Dec 08 '10 at 10:23

SQL - column from all rows returned from a Join into a single column?

2 Answers2

Linked