Create sql that will give me the product name separated by a semicolon?

Question

I have a product table as shown below. I would like to create sql that will give me the product name, separated by a semicolon.

ProductName  AccountExpert
--------------------------
Tea          JohnSmith
Banana       GarySulvan
Water        JohnSmith
Candy        BobbySimmons
ConfecItem   BobbySimmons
Bread        JohnSmith
Soda         JohnSmith

Sql output should like

AccountExpert  Products
-----------------------
JohnSmith      Tea; Water; Bread; Soda
GarySulvan     Banana
BobbySimmons   Candy; ConfecItem

Answer 1

In MS SQL, you can use FOR XML, using the Stuff function to remove the redundant delimiter. Unfortunately, there is no concat function group, as with MySQL.

CREATE TABLE #ProductExpert (ProductName nvarchar(20), AccountExpert nvarchar(20))

INSERT INTO #ProductExpert(ProductName, AccountExpert) SELECT
'Tea',          'JohnSmith'     UNION ALL SELECT
'Banana',       'GarySulvan'    UNION ALL SELECT
'Water',        'JohnSmith'     UNION ALL SELECT
'Candy',        'BobbySimmons'  UNION ALL SELECT
'ConfecItem',   'BobbySimmons'  UNION ALL SELECT
'Bread',        'JohnSmith'     UNION ALL SELECT
'Soda',         'JohnSmith'

SELECT DISTINCT
    ae.AccountExpert,
    Stuff((
        SELECT
             '; ' + p.ProductName
        FROM
            #ProductExpert AS p
        WHERE
            ae.AccountExpert = p.AccountExpert
        ORDER BY
            p.ProductName
        FOR XML PATH('')
    ), 1, 2, '') AS Products
FROM
    #ProductExpert AS ae
ORDER BY
    ae.AccountExpert

DROP TABLE #ProductExpert

Answer 2

if you are using MySQL then use GROUPing and GROUP_CONCAT:

SELECT AccountExpert, GROUP_CONCAT(ProductName SEPARATOR '; ')
FROM ProductExperts
GROUP BY AccountExpert

Answer 3

:

group_concat MySQL Microsoft SQL Server 2005?

The bottom line is that there is no really elegant way in SQL Server 2008

Answer 4

Please see http://blog.namwarrizvi.com/?p=140

Its what you want to do, but instead ,