CODEWITHSUNDEEP
sqlsql-server-2008
HL8
HL8

Reputation: 1419

sql column which contains a comma separate values

How can I combine multiple rows into one row and have a column which contains a comma separate values?

Example: Originally my SQLresult would return the following using a simple select script like 

select order_no, item_no, item_description 
from orders...

order_no      item_no  item_description
1234          5        toaster
1234          6        hair dryer

Instead I would like to return the results into the below (having the item_description listed in the same order as the item_nos?

order_no     item_nos    item_descriptions
1234         5, 6        toaster, hair dryer

And could I return results like this?

order_no    item_nos_descriptions
1234        5 - toaster, 6 - hair dryer

By the way I'm using SQL 2008...

Upvotes: 0

Views: 979

Answers (5)

Seph
Seph

Reputation: 8693

For SQL Server 2005 and up, here's the way that I usually do it without using Recursive CTE

DECLARE @T TABLE
(
order_no int,
item_no int,
item_description nvarchar(50)
)


INSERT INTO @T VALUES (1234, 5, 'toaster')
INSERT INTO @T VALUES (1234, 6, 'hair dryer')

SELECT order_no,
    STUFF(
        (
            SELECT ', ' + CAST(item_no AS VARCHAR) AS [text()]
            FROM @T As MyItem
            WHERE MyItem.order_no = MyTable.order_no
            FOR XML PATH('')
        ), 1, 2, '' ) AS item_nos,
    STUFF(
        (
            SELECT ', ' + CAST(item_no AS VARCHAR) AS [text()]
            FROM @T As MyItem
            WHERE MyItem.order_no = MyTable.order_no
            FOR XML PATH('')
        ), 1, 2, '' ) AS item_descriptions
FROM @T AS MyTable
GROUP BY order_no

This yields:

Result Set (1 item)
order_no | item_nos | item_descriptions |
1234     | 5, 6         | 5, 6

The STUFF removes the last ', ' from the string.

The other way to do this is with recursive CTE, but I think the above will do...

Upvotes: 2

Lee
Lee

Reputation: 13542

Check out the group_concat function (docs). 

select 
  order_no,
  group_concat(item_no ORDER BY item_nos ASC SEPARATOR ', ') as item_nos,
  group_concat(item_description ORDER BY item_no ASC SEPARATOR ', ') 
    as item_descriptions
from orders
group by order_no

will give something like this:

order_no     item_nos    item_descriptions
1234         5, 6        toaster, hair dryer

For the second form you requested, it would look something like this:

select 
  order_no,
  group_concat( concat(item_no,' - ',item_description 
    ORDER BY item_no ASC SEPARATOR ', ') 
  as item_nos_descriptions
from orders
group by order_no

Upvotes: 1

My Other Me
My Other Me

Reputation: 5117

For MySQL you could do this:

SELECT order_no, 
   GROUP_CONCAT(CONCAT(item_no,' - ',item_description) ORDER BY item_no ASC SEPARATOR ', ')
FROM Orders
GROUP BY order_no

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try GROUP_CONCAT if you are using mySQL:

SELECT order_no, 
   GROUP_CONCAT(item_no ORDER BY item_no ASC SEPARATOR ','),
   GROUP_CONCAT(item_description ORDER BY item_no ASC SEPARATOR ',')
FROM Orders
GROUP BY order_no

In this way you can keep the original normalized DB schema and still get the following result:

order_no     item_nos    item_descriptions
1234         5, 6        toaster, hair dryer

Upvotes: 0

Daniel Lyons
Daniel Lyons

Reputation: 22803

I think you should heed @pst if you can.

That said, most relational databases have a function to do exactly this. In MySQL it's group_concat. In Oracle it's wm_concat. In PostgreSQL it's string_agg. Notice it's rather unstandardized.

To use it, you would do something like this:

SELECT order_no, string_agg(item_description, ',') 
FROM orders 
INNER JOIN line_items ON line_item.order_id = order.id
GROUP BY order_no;

Note that not all databases have a way to get from CSV back to rows. This is something I know PostgreSQL can do. I would expect Oracle to be able to do it, but haven't checked, and I'm fairly sure that MySQL cannot, but could be mistaken.

Upvotes: 0

Related Questions