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

Question

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

What SQL engine are you using? (This is important because concatenation aggregation is actually pretty tricky and the answer may depend on the engine and version you have.) — Michael Haren, Jul 14 '11 at 15:55
you should do a bit more research, and some effort before you post your question here. This is not a Q&A website, it's where people will help you if you've put some effort and are stuck somewhere ! — painotpi, Jul 14 '11 at 15:55
@Tarun showing some effort is a nice gesture, sure, but...it's *not* a Q&A website?! Of course it is! — Michael Haren, Jul 14 '11 at 15:57
@Tarun: You are wrong; this is not a trivial question. It's similar to [this one](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-sub). — SLaks, Jul 14 '11 at 15:58
possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Michael Haren, Jul 14 '11 at 15:59

score 1 · Answer 1 · answered Jul 14 '11 at 15:57

1

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

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

answered Jul 14 '11 at 15:57

keymone

8,006
1
28
33

oh just noticed that you tagged question as sql-server-2008, quick googling returned this post: http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html hope that helps – keymone Jul 14 '11 at 15:59

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

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

edited May 23 '17 at 11:52

Community

1
1

answered Jul 14 '11 at 16:00

Brian Hoover

7,861
2
28
41

Evil Pigeon · Accepted Answer · 2011-07-14T16:21:29.800

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

score 0 · Answer 4 · answered Jul 14 '11 at 15:58

0

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

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

answered Jul 14 '11 at 15:58

Lourens

1,510
1
13
27

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

4 Answers4