How to select column value as Comma Separated Values string in SQL

Question

I am having two table product and sale in sql database .

productId   name 
1           a
2           b
3           c

Sale 
ID  productId   sale
1      1        5
2      2        10
3      1        20
4      1        50

Now I want using of both table I need the out put in this format using sql :

productId   name    sale
1            a       5,20,50
2            b       10

can u plz any one tell me how i can get my sale column vale as comma separted values using sql query.

SMA · Accepted Answer · 2015-02-07 15:32:01Z

4

You can do a join between product and sales as below:

SELECT P.productId, P.name, GROUP_CONCAT(sale) AS 'sale'
FROM product AS P INNER JOIN sale AS S
ON S.productID=P.productID
GROUP BY P.productId;

See fiddle here

answered Feb 7, 2015 at 15:32

SMA

37.2k8 gold badges53 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christopher McGowan Over a year ago

Nice example! I'll have to try fiddle on the next question I answer.

SMA Over a year ago

Thanks. Yeah that proves that your query indeed works and an insurance for OP ;)

sam140 Over a year ago

suppose if i m using oracle then what is the right approach to getting the same out put can u plz guide me ...its only for just understanding i m asking

SMA Over a year ago

See this blog for details.

Christopher McGowan · Accepted Answer · 2015-02-07 15:29:35Z

0

You can use GROUP_CONCAT if you are using MySQL:

SELECT P.productId, P.name, S.GROUP_CONCAT(sale)
FROM product AS P
LEFT JOIN sale AS S
ON S.productID=P.productID
GROUP BY P.productId;

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

answered Feb 7, 2015 at 15:29

Christopher McGowan

1,40111 silver badges10 bronze badges

7 Comments

Jean-François Savard Over a year ago

why use a LEFT JOIN here ?

Christopher McGowan Over a year ago

LEFT JOIN is a more specified way of stating the join we are doing. An INNER JOIN will omit products that don't have any sales from the result.

Christopher McGowan Over a year ago

By the way, because you specify productID in both tables by the same name, you can say USING (productID) instead of having to say ON S.productID=P.productID

Jean-François Savard Over a year ago

Yeah I know about the ` using` keyword, and I also know how the left join work but OP did not specify he want products that don't have any sale.

Christopher McGowan Over a year ago

Right; one should always default to showing more information than less. If using an INNER join, the OP may not become aware that it is not showing all products even if they desired that, whereas with LEFT, the OP will become aware that it does show them, and can then change the behavior to suit their more specific requirements.

|

Collectives™ on Stack Overflow

How to select column value as Comma Separated Values string in SQL

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related