CODEWITHSUNDEEP
sqlsql-server-2008
Mike Ozark
Mike Ozark

Reputation: 109

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

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

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

Sql output should like this

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

Upvotes: 5

Views: 114

Answers (4)

Evil Pigeon
Evil Pigeon

Reputation: 1976

With MS SQL you can use FOR XML, using the Stuff function to remove the excess delimiter. Unfortunately, there's no group concat function 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

Upvotes: 1

Brian Hoover
Brian Hoover

Reputation: 7991

You can try some of these ideas:

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Bottom line is that there isn't a really elegant way to do this in SQL-Server 2008

Upvotes: 1

Lourens
Lourens

Reputation: 1518

Please have a look at http://blog.namwarrizvi.com/?p=140

Its what you want to do, but with a , instead

Upvotes: 0

keymone
keymone

Reputation: 8104

if you're using MySQL then use GROUPing and GROUP_CONCAT:

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

Upvotes: 1

Related Questions