Comma delimited string in a Subquery

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 6, 2012 at 7:10 pm

  #262465

  Can this be done?

  I can get a query to return a comma delimited string of results using Coalesce but it doesn't seem to work as a subquery.

  Is there a way to get it to work:

  DECLARE @Summary varchar(2000)

  SELECT TOP 1000

  [ModelId] ,

  [GroupId] ,

  ( SELECT @Summary = COALESCE(@Summary + ', ', '') +

  CONVERT(varchar,model.Year) + ' - ' + model.Code

  FROM model WHERE model.ModelId = h.modelID

  ) temp

  FROM [History] h

  I assume this doesn't work as the subquery doesn't return any values but stores the value in the variable.

  Is there a way to get this to work or something like it?

  Thanks,

  Tom

• imex

  Ten Centuries

  Points: 1234

  More actions

  June 6, 2012 at 7:43 pm

  #1497973

  Try:

  SELECT TOP 1000

  [ModelId] ,

  [GroupId] ,

  SUBSTRING(( SELECT ', ' + CONVERT(varchar, model.Year) + ' - ' + model.Code

  FROM model

  WHERE model.ModelId = h.modelID

  FOR XML PATH('') ), 3, 200000) as TempCol

  FROM [History] h

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 7, 2012 at 7:42 am

  #1498172

  Please don't cross post. It just fragments replies. You will get better answers in a single thread. Please direct all replies to the first one here. http://www.sqlservercentral.com/Forums/Topic1312260-392-1.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/