0

Consider the data below.

account     product sales

account1    prod1   10.0
account1    prod1   15.0
account1    prod1   12.0
account1    prod1   5.0
account1    prod1   78.0
account1    prod1   45.0
account1    prod1   34.0
account1    prod1   9.0
account1    prod1   19.0
account1    prod2   12.0
account1    prod2   11.0
account1    prod2   14.4
account1    prod2   6.0
account1    prod2   93.6
account1    prod2   54.0
account1    prod2   25.0
account1    prod2   7.0
account1    prod2   22.8
account2    prod3   23.0

Now i want to iterate through the unique products and want to return the comma separated list of sales for unique products.

I want the output to be like

i.e

Prod1

12.0, 16.0, 45.0, 38.0, 69.0, 42.0, 45.0, 65.0, 28.0, 95.0, 35.0, 25.0

Prod2

12.0, 16.0, 45.0, 38.0, 69.0, 42.0, 45.0, 65.0, 28.0, 95.0, 35.0, 25.0

where this 2nd row is the comma separated list of sales for that product.

What is the best way to achieve this in sql server 2008r2 ?

Should i use cursor ?

Sachin Trivedi
  • 2,033
  • 4
  • 28
  • 57
  • http://stackoverflow.com/q/15841319/3630826 – Jaugar Chang Oct 29 '14 at 12:09
  • Asking for a fast reply will not speed up the process. It will just annoy people. Besides this has been answered so many times – t-clausen.dk Oct 29 '14 at 12:13
  • @t-clausen.dk. i am sorry , But can you provide direct link or example which can help me to understand on how to achieve this ? – Sachin Trivedi Oct 29 '14 at 12:32
  • 1
    Here is an [example](http://stackoverflow.com/questions/23575481/group-and-tally-values-for-each-record-in-sql/23576528#23576528) – t-clausen.dk Oct 29 '14 at 12:46
  • try this : SELECT PRODUCT, STUFF(( SELECT ', ' + SALES FROM YourtableName FOR XML PATH ('')) ,1,2,'') AS NameValues FROM YourtableName GROUP BY PRODUCT – Sunil Naudiyal Oct 29 '14 at 12:54
  • @SunilNaudiyal this is returning me the sales data for all the products. It should return the sales values for particular product only. – Sachin Trivedi Oct 29 '14 at 13:13
  • 1
    Use where clause with query: SELECT PRODUCT, STUFF(( SELECT ', ' + SALES FROM YourtableName FOR XML PATH ('')) ,1,2,'') AS NameValues FROM YourtableName WHERE PRODUCT='prod1' GROUP BY PRODUCT – Sunil Naudiyal Oct 30 '14 at 06:28

0 Answers0