Trying to display a column's different values with a same ID in a single row.

hi all,

 

is there anyway can select columns different values having same id in row?

below data

 

wo num      productcode location id

w-1000       aaa              a1

w-1000       aaa              a2

w-1000       aaa              a4

w-1001       aab              a3

w-1002       aac              a7

 

and data below trying achieve

w-1000        aaa             a1,a2,a4

w-1001       aab              a3

w-1002       aac              a7

 

thanks

 

cutie

 

 

here possible solution:

select vonum, product, stuff((select ',' + p.locationid products p p.vonum = p1.vonum , p.product = p1.product order p.locationid  xml path('')),1,1,'') locations products p1 group vonum, product order vonum, product 

for explanation of technique take at http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Getting started with SQL Server