Get string from query results instead of rows

Question

I am using SQL Server 2012. I need to concatenate results from query below into one long string. So, instead of getting row results I need only one string. I would like to populate @TEMPS_MEASURE with results.

Currently, results looks like this:

Instead of this, I would like only one string returned with <br/> between columns, like this:

H 1 topla kuhinja (2 - 6 °C):<br/>Temperatura: 5.00°C<br/>H 2 hladna kuhinja (2 - 6 °C):<br/>Temperatura: 4.00°C<br/>

How should I change query?

Query:

DECLARE @TEMPS_MEASURE NVARCHAR(500) = NULL
SELECT
 CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN NULL ELSE COLI.COLI_NME + ':<br/>' END AS EQ_NAME 
, CASE HATE.HATE_TE1 WHEN NULL THEN NULL ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C<br/>' END AS TEMP
, CASE HATE.HATE_AC1 
        WHEN NULL THEN NULL 
        WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo<br/>'
        WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*)<br/>'
        WHEN '3' THEN 'Ukrep: Dodatno ogrejemo<br/>'
  END ACTION_UPON
, CASE HATE.HATE_TE2 WHEN NULL THEN NULL ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C<br/>' END AS TEMP_CENTER   
FROM CAHATE HATE
INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
WHERE HACC_KEY = 160001417

SOLUTION:

I've created function with FOR XML PATH:

ALTER FUNCTION dbo.f_CAHATE_Get_Temperatures (@iHACC_KEY INTEGER)
RETURNS NVARCHAR(500)
AS BEGIN



DECLARE @TEMPS_MEASURE NVARCHAR(500) = NULL
SELECT @TEMPS_MEASURE = (SELECT
    CONCAT(
         (CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN '' ELSE COLI.COLI_NME + ':' END)
        , (CASE HATE.HATE_TE1 WHEN NULL THEN '' ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C;' END)
        , (CASE HATE.HATE_AC1 
                WHEN NULL THEN '' 
                WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo;'
                WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*);'
                WHEN '3' THEN 'Ukrep: Dodatno ogrejemo;'        
          END)
        , (CASE HATE.HATE_TE2 WHEN NULL THEN '' ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C;' END)
    )
FROM 
    CAHATE HATE
    INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
WHERE 
    HACC_KEY = @iHACC_KEY
    for xml path('') ) 


    RETURN @TEMPS_MEASURE
END

Answer 1

As posted above in question, this is solution to the problem. I've created function with FOR XML PATH:

ALTER FUNCTION dbo.f_CAHATE_Get_Temperatures (@iHACC_KEY INTEGER)
RETURNS NVARCHAR(500)
AS BEGIN



DECLARE @TEMPS_MEASURE NVARCHAR(500) = NULL
SELECT @TEMPS_MEASURE = (SELECT
    CONCAT(
         (CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN '' ELSE COLI.COLI_NME + ':' END)
        , (CASE HATE.HATE_TE1 WHEN NULL THEN '' ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C;' END)
        , (CASE HATE.HATE_AC1 
                WHEN NULL THEN '' 
                WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo;'
                WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*);'
                WHEN '3' THEN 'Ukrep: Dodatno ogrejemo;'        
          END)
        , (CASE HATE.HATE_TE2 WHEN NULL THEN '' ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C;' END)
    )
FROM 
    CAHATE HATE
    INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
WHERE 
    HACC_KEY = @iHACC_KEY
    for xml path('') ) 


    RETURN @TEMPS_MEASURE
END

Answer 2

Here is an example:

;WITH T AS 
(   
    SELECT      
        1 AS Id,
        CONCAT(
             (CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN '' ELSE COLI.COLI_NME + ':<br/>' END)
            , (CASE HATE.HATE_TE1 WHEN NULL THEN '' ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
            , (CASE HATE.HATE_AC1 
                    WHEN NULL THEN '' 
                    WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo<br/>'
                    WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*)<br/>'
                    WHEN '3' THEN 'Ukrep: Dodatno ogrejemo<br/>'        
              END)
            , (CASE HATE.HATE_TE2 WHEN NULL THEN '' ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
        ) AS String
    FROM 
        CAHATE HATE
        INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
    WHERE 
        HACC_KEY = 160001417
)

SELECT 
   STUFF((SELECT '; ' + US.String 
          FROM T US
          WHERE US.Id = SS.Id
          FOR XML PATH('')), 1, 1, '') [FinalString]
FROM T SS
GROUP BY SS.Id, SS.String
ORDER BY 1

You can remove the separator ; if not required.

Answer 3

Please try below query. COALESCE is SQL provided function which will solve your problem.

I have first join your all columns row by row and then using COALESCE convert rows to one row.

select * into #Temp 
from 
(
  select (isnull(EQ_Name,'')+isnull(Temp,'')+isnull(Action_Upon,'')+isnull(TempCenter,'') )as data 
  from CAHATE 
) as TempData

DECLARE @result nVARCHAR(MAX)

SELECT @result = COALESCE(@result + ', ' + [data], [data]) 
FROM #Temp

PRINT @result

Drop Table #Temp

Answer 4

You can also use CONCAT function as it was introduced in SQL Server 2012 to combine different string values into one. It also handles different data types to convert into string type. So, you will not have type mismatch exception.

SELECT
    CONCAT(
         (CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN '' ELSE COLI.COLI_NME + ':<br/>' END)
        , (CASE HATE.HATE_TE1 WHEN NULL THEN '' ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
        , (CASE HATE.HATE_AC1 
                WHEN NULL THEN '' 
                WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo<br/>'
                WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*)<br/>'
                WHEN '3' THEN 'Ukrep: Dodatno ogrejemo<br/>'        
          END)
        , (CASE HATE.HATE_TE2 WHEN NULL THEN '' ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
    )
FROM 
    CAHATE HATE
    INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
WHERE 
    HACC_KEY = 160001417

Answer 5

You need to combine 4 of your columns together

SELECT
 (CASE WHEN HATE.HATE_TE1 IS NULL AND HATE.HATE_AC1 IS NULL AND HATE.HATE_TE2 IS NULL THEN '' ELSE COLI.COLI_NME + ':<br/>' END)
+ (CASE HATE.HATE_TE1 WHEN NULL THEN '' ELSE 'Temperatura: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE1,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
+ (CASE HATE.HATE_AC1 
        WHEN NULL THEN '' 
        WHEN '1' THEN 'Ukrep: Zavržemo jed / živilo<br/>'
        WHEN '2' THEN 'Ukrep: Izmerimo središčno temperaturo jedi / živila (TS*)<br/>'
        WHEN '3' THEN 'Ukrep: Dodatno ogrejemo<br/>'        
  END)
+ (CASE HATE.HATE_TE2 WHEN NULL THEN '' ELSE 'Temperatura središča: ' + CONVERT(VARCHAR(20), CAST(ROUND(HATE.HATE_TE2,2,0) AS NUMERIC(10,2))) + '°C<br/>' END)
FROM CAHATE HATE
INNER JOIN CACOLI COLI ON COLI.COLI_KEY = HATE.COLI_KEY
WHERE HACC_KEY = 160001417

Note: I change When NULL then NULL to When NULL then '' to make sure the "+" work.