concatenating column values in a row

[chopstix21]

i need to concatenate x values which are grouped by id, ordering them by y column from subquery.
Subquery looks like this
id x y
1 1 1
1 2 3
1 3 2
2 1 null
2 2 1
3 1 1
3 2 2
4 1 null
4 2 null
final results should look like
id result
1 1,3,2
2 2
3 1,2
4 null

 

[born2program]

The following should get you what you need. Just replace "YourTable" in the From statements with the name of your table.

SELECT A.ID, 
Result=STUFF((
	      select ',',convert(varchar,B.X)
	      From YourTable B
	      Where B.ID=A.ID
	      Order by B.Y
	      for XML Path('')
	      ),1,1,'')
From YourTable A
Group By A.ID
Order By A.ID

 

[drlex]

To match the OP's result set

SELECT A.ID, 
Result=STUFF((
	      select ',',convert(varchar,B.X)
	      From YourTable B
	      Where B.ID=A.ID [b][red] AND B.Y is not NULL[/red][/b]
	      Order by B.Y
	      for XML Path('')
	      ),1,1,'')
From YourTable A
Group By A.ID
Order By A.ID

soi là, soi carré