BizTalk SOA Evangelist

Recently I encountered a problem wherein I need to concatenate and return the row values in single field. I was getting the results as below in rows and I needed to return the concatenated values of these row values.

In fact I was not having any column like LoadNumber which could have worked as ID and the implementation should have been a bit easy.

I am mentioning two different solutions below. On works fine in case of standalone query and the other one is very straight forward. I would suggest to follow the second one. But it all depends on you requirement as what and where you need to implement this. I believe both the below mention queries should be helpful.

FIRST QUERY:

DECLARE @MYXML XML
SET @MYXML= (SELECT ( SELECT DISTINCT TemperatureCode
                               FROM Results WITH(NOLOCK) where 'Mention the condition'
                               FOR XML Path('TEMPPROT'), BINARY BASE64,TYPE)
SELECT (
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[1]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[2]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[3]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[4]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[5]','varchar(5)'),'')
  
                )
AS 'ConcatenatedTemp' 
FROM @MYXML.nodes('/TEMPPROT/TemperatureCode') a(b)

SECOND QUERY:

SELECT (SELECT distinct TemperatureCode + ''
                  FROM Results WITH(NOLOCK) where 'Mention the condition'
                  FOR XML Path(''), BINARY BASE64,TYPE)
                  )
AS ConcatenatedTemp