How to choose a columns of a select statement based on conditions?

Question

I have to create an SQL Server 2005 query which checks for the value of one attribute in the table and based on its value, select different sets of columns. How can I do that?

for e.g.

In table 'car', if the values of 'type' attribute are 1 and 2 when type = 1, i want to execute a select 'query1' with 3 columns. when type = 2, i want to execute another select 'query2' with 4 other columns.

How do I do that? Please help.

Answer 1

I think you're looking at a Stored Procedure with an If statement. CASE will work, but it can't change the number of columns returned.

Answer 2

SELECT
  Col1 = CASE WHEN Type = 1 THEN (SELECT Null FROM T1)
         ELSE (SELECT Col1 FROM T2) END
  , Col2 = CASE WHEN Type = 1 THEN (SELECT Col1 FROM T1)
           ELSE (SELECT Col2 FROM T2) END
  , Col3 = CASE WHEN Type = 1 THEN (SELECT Col2 FROM T1)
           ELSE (SELECT Col4 FROM T2) END
  , Col4 = CASE WHEN Type = 1 THEN (SELECT Col3 FROM T1)
           ELSE (SELECT Col4 FROM T2) END
FROM Cars

If you would show us the DDL of all tables involved, you'd probably get a better answer or a different (read better) approach.