CODEWITHSUNDEEP
sql-server
Alvin Quezon
Alvin Quezon

Reputation: 13

SQL CASE Statement Change Column name in THEN clause

Is there anyway that I can change column name through THEN clause in CASE STATEMENT. This is how my code looks but it is not working.

 SELECT 
     CASE 
        WHEN @IDWeeklySpecial = '107' 
           THEN 
              CASE 
                 WHEN ISNULL(v.blnLeftHandDrive, 0) = 1 
                    THEN [HANDEDNESS] = 'LHD' 
                    ELSE [HANDEDNESS] = 'RHD' 
           ELSE 
              CASE 
                 WHEN ISNULL(v.blnLeftHandDrive, 0) = 1
                    THEN STEERING = 'LHD' 
                    ELSE STEERING = 'RHD' 
             END 
     END

I want this result

My Ideal Result

Is this achievable? If so how?

Upvotes: 1

Views: 28487

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

case is an expression. You can name the column before or after an expression, but not in the middle.

Also, a SQL statement needs to return a fixed set of columns, including the column name.

So, you cannot do what you want with a simple SQL statement. You could use dynamic SQL:

declare @sql nvarchar(max);
set @sql = '
SELECT (CASE WHEN COALESCE(v.blnLeftHandDrive, 0) = 1
             THEN 'LHD'
             ELSE 'RHD'
         END) as [columnname]
. . . ';

set @sql = replace(@sql, '[columnname]',
                   (case when @IDWeeklySpecial = '107' then 'HANDEDNESS' else 'STEERING' end)
                  );

exec sp_executesql @sql;

Note that the . . . is for the rest of your SQL query.

Upvotes: 2

Gerry
Gerry

Reputation: 67

It is not possible to assign differing column names in your query, because all vertical data values in the result set belong to the same column.

If you are just trying to alias a column name while using the CASE expression, ensure that the AS is used after the END keyword. Others have mentioned this, but it was the problem in my case. Changed it to something like this and it worked: 

CASE  
    WHEN table1.Name LIKE '%John%' THEN 'Some John Person' 
    WHEN table1.Name <> 'NULL' THEN table1.Name
    ELSE 'Nobody' 
END AS NewAliasColumnName

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

No, it's not possible... if you are asking about dynamically modifying the column name in the resultset.

The CASE expression returns a value. To a specify the column name in the resultset, assign an alias.

  SELECT CASE ... END AS mycolname
       , ... 
    FROM ...

The column name is determined when the statement is parsed and prepared. The execution of the statement cannot modify the column name.

(It's possible I didn't understand the question. It's not clear what OP is tying to achieve.)

Upvotes: 4

Randeep Singh
Randeep Singh

Reputation: 1018

I can't digest your select statement, but assume its inline case

SELECT item_name = CASE WHEN item_name = 'sometext' THEN item_id ELSE item_name END AS MyColumnName

Upvotes: 0

Related Questions