sql column which contains a comma separate values

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...

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.

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 

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