How to get column data separated by coma in grouping in SQL server 2008

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi guys,

i have one table in sql

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

i want to do its group on type, but in variety it should show all data related to its type.

for eg: for apple, it must show. gala, fuji, limbertwing

What I have tried:

I want to display this as a result.

+--------+----------------------------+-------+
| type   | variety                    | price |
+--------+----------------------------+-------+
| apple  | gala, fuji, limbertwig     |  5.9  | 
| orange | valencia,navel             | 12.95 |  
| pear   | bradford,bartlett          |  8.19 | 
| cherry | bing, chelan               |  8.88 | 
+--------+----------------------------+-------+

Thanks

Posted 27-Apr-16 2:16am

abdul subhan mohammed

Updated 27-Apr-16 2:46am

Add a Solution

Comments

Thanks7872 27-Apr-16 8:32am

'What have you tried' section is not intended to show what you want. Its for what you have done till now in order to resolve the issue. You have put zero effort. Did you tried google even once?

Try to search for 'get comma separated values using group by'. Even first link is enough to get the issue resolved.

Suvendu Shekhar Giri 27-Apr-16 8:37am

Very true.
My virtual +5 (Y)

2 solutions

Solution 2

Hi you can use this query:

select YT.type,
(
STUFF((SELECT ',' + EQ.variety FROM YourTable EQ WITH (NOLOCK) WHERE EQ.type=YT.type FOR XML PATH('')), 1, 1, '')
) variety,

sum(YT.price) Price

from yourtable YT
group by type

Please let me know if you are getting any problem.

Ashish Nigam

Posted 27-Apr-16 2:46am

Nigam,Ashish

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-04-27T02:41:00

Solution 1

Try:

SQL

SELECT Type, Variety =
   STUFF((SELECT ',' + Variety
      FROM myTable b
      WHERE b.Type = a.Type
      FOR XML PATH('')), 1, 1, ''),
   SUM(Price) AS Price
FROM myTable a
GROUP BY Type

Posted 27-Apr-16 2:41am

OriginalGriff

Comments

Richard Deeming 27-Apr-16 9:04am

If the Variety column contains any "special" characters, they will end up being XML-encoded. To avoid that, you should use FOR XML PATH(''), TYPE in the inner query, and add .value('.', 'varchar(max)') after it.

SELECT Type, Variety =
   STUFF((SELECT ',' + Variety
      FROM myTable b
      WHERE b.Type = a.Type
      FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''),
   SUM(Price) AS Price
FROM myTable a
GROUP BY Type

Concatenating Row Values in Transact-SQL[^]

OriginalGriff 27-Apr-16 9:19am

Good point!