Sql-server – How to write this query in SQL Server Compact in more elegant way

sql-server-compactt-sql

The query is :

 Select  
        Case AStatus  
            When 1 then 'Success'
            When 0 then 'Faliure'
        END,
        Case AStatus  
            When 1 then 'AStatus'
            When 0 then 'AStatus'
        END,
        Case AStatus  
            When 1 then 'Expected:1'  
            When 0 then 'Recived: 0'  
        END
    From Statuses  
    Where LocalPath= 'c:\Status'

The original query that didn't work well in SQL Server Compact (and equal to above query) is:

Select  
   Case AStatus  
      When 1 then 'Success', 'AStatus', 'Expected:1'  
      When 0 then 'Faliure', 'AStatus', 'Recived: 0'    
   end
From Statuses  
Where LocalPath= 'c:\Status

Best Answer

There is no multi value CASE statement in T-SQL. However, you could do a join to a constant value row source like this:

SELECT *
  FROM dbo.Statuses AS S
  LEFT JOIN (VALUES(1,'Success','AStatus','Expected:1'),
                   (0,'Failure','AStatus','Received:0')
            ) AS SV(AStatus, Status, Reason, Message)
    ON S.AStatus = SV.AStatus;
Related Question