CODEWITHSUNDEEP
sqlt-sql
bircastri
bircastri

Reputation: 2165

SQL get value of result of query by one row

I have this query:

SELECT 
      R.ID
     ,O.DisplayName
     ,[Value]
FROM AA_V_PHR_CCD_ResultsXResults R 
INNER JOIN AA_V_PHR_CCD_ResultsObservationXLANGUAGES O
           ON R.IDResultObservation = O.ID  
WHERE IDResults = 149 AND O.IDLanguage = 2

So this is the result of this query:

ID  DisplayName Value
1604    HBsAg   0.140
1605    HBsAb   0.000
1606    HCV 0.020

I want have this result if it is possible:

1604 HBsAg: 0.140 1605 HBsAb: 0.00 etc

It is possible to do this

Upvotes: 0

Views: 52

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You should use STUFF function as below:

SELECT STUFF(
            (
                SELECT ' '+CONVERT(VARCHAR(MAX), ID)+' '+DisplayName+' : '+CONVERT(NVARCHAR(MAX), Value)
                FROM <table_name> FOR XML PATH('')
            ), 1, 1, '') [data];

Output :

data
1604 HBsAg : 0.14 1605 HBsAb : 0.00 1606 HCV   : 0.02

Upvotes: 1

Shubham Pandey
Shubham Pandey

Reputation: 1019

This could solve the problem without using XML, if that helps

DECLARE @Temp NVARCHAR(MAX) = ''

SELECT @TEMP = CONCAT(@Temp, CAST(ID AS VARCHAR(4)), ' ', DisplayName, ' :', CAST(Value AS VARCHAR(5), ' ')
FROM Table

SELECT @Temp

Upvotes: 0

mucio
mucio

Reputation: 7119

You could try this:

    SELECT stuff(
                 (
                   SELECT cast(',' as varchar(max)) + R.ID || ' ' || O.DisplayName  || ' ' || [Value]
                     FROM AA_V_PHR_CCD_ResultsXResults R 
               INNER JOIN AA_V_PHR_CCD_ResultsObservationXLANGUAGES O
                       ON R.IDResultObservation = O.ID  
                    WHERE IDResults = 149 
                      AND O.IDLanguage = 2    for xml path('')
                 ), 
               1, 1, '') AS my_results
      FROM AA_V_PHR_CCD_ResultsXResults R 
INNER JOIN AA_V_PHR_CCD_ResultsObservationXLANGUAGES O
        ON R.IDResultObservation = O.ID  
     WHERE IDResults = 149 
       AND O.IDLanguage = 2

Upvotes: 0

Related Questions