T-SQL – Case statement based on a subquery

by Otto on July 1, 2008

Today I needed to write a subquery to get additional data for a file we export daily.  However, I didn’t need the actual data, I just needed to set a value based on the column in the subquery.  I couldn’t find any examples via the Google, so I figured I’d help others out that might be looking.

Here is what SQL Server 2005 says about the CASE t-sql statement:

USE AdventureWorks;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

All we need to do is replace ProductLine above with our subquery and we’re good to go.  Here’s a sample with a subquery.

USE AdventureWorks;
GO
SELECT   ProductNumber,
      CASE (SELECT SubQueryField FROM SubQueryTable WHERE SubQueryTable.key = Production.key)
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END as SubQueryField,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Hope that helps.

{ 1 comment }

Lydia May 24, 2012 at 9:46 am

4 years later and I found this helpful. Thanks for posting

Comments on this entry are closed.

Previous post:

Next post: