0

I have two row values from table C: 

Select Name FROM Table C Where AccountID = 123


      COL1
Row 1 |Ricky|
Row 2 |Roxy |

I want to be able to select both of these two values in a SubQuery that will be used in a larger query. So that it displays "Ricky, Roxy"

How can this be done without declaring a variable?

SSD
  • 1,373
  • 2
  • 13
  • 20
Sebastian Roces
  • 92
  • 1
  • 8
  • 2
    Google: "SQL Server string aggregation" – Gordon Linoff Oct 16 '15 at 14:33
  • 1
    http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation – Aaron Bertrand Oct 16 '15 at 14:37
  • What about if I was to use a variable? – Sebastian Roces Oct 16 '15 at 14:44
  • Don't use a variable. The behaviour is not documented, and you can get unexpected results depending on internal access paths. A recently answered [a question](http://stackoverflow.com/q/32267590/1048425) where the user had encountered this exact unexpected behaviour when using a variable to concatenate strings. Just read Aaron's article, it not only contains numerous solutions, it also compares performance on all of them. What more could you want? – GarethD Oct 16 '15 at 14:55

1 Answers1

0
SELECT COL1 = STUFF ((SELECT ',' + COL1 FROM tableC WHERE AccountID=123
FOR XML PATH(''), Type).value('.[1]','nvarchar(max)'),
1,1,'')

This will return all account 123 COL1 values as one column, with commas separating values.

Here is a SQL Fiddle

devlin carnate
  • 8,309
  • 7
  • 48
  • 82
  • @SebastianRoces hard to say why without knowing your table schema. i've updated my answer with a SQL Fiddle showing it works in SQL Server 2008. – devlin carnate Oct 16 '15 at 15:15