## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# SQL 2008 How to specify column name when using case statement

Posted on 2014-01-15
3,288 Views
This should be simple... How to specify a column name "as ....." when using a case statement (in bold below).

COALESCE(T1.POSTCODE_FW, '') as 'Zip Code',
COALESCE(T1.VENDOR_SPECIALTY_FW, '') as 'Vendor Specialty',
COALESCE(T1.VENDOR_TYPE_FW, '') as 'Vendor Type',

CASE
WHEN T1.SUPSTATUS_FW = '0' THEN ''
WHEN T1.SUPSTATUS_FW = '1' THEN 'YES'
ELSE ''
END,

COALESCE(CONVERT(varchar(10), T2.START_DATE_FW, 101), '') as 'Start Date',
COALESCE(CONVERT(varchar(10), T2.END_DATE_FW, 101), '') as 'End Date',
0
Question by:66chawger

LVL 75

Accepted Solution

Aneesh Retnakaran earned 250 total points
ID: 39783066
CASE
WHEN T1.SUPSTATUS_FW = '0' THEN ''
WHEN T1.SUPSTATUS_FW = '1' THEN 'YES'
ELSE ''
END   As NewColumnName ,
0

LVL 34

Assisted Solution

Brian Crowe earned 125 total points
ID: 39783104
CASE T1.SUPSTATUS
WHEN '1' THEN 'YES'
ELSE ''
END AS SomeColumnName,
0

LVL 11

Assisted Solution

John_Vidmar earned 125 total points
ID: 39783303
You can assign a fieldname two ways
1. fieldname = calculation, or
2. calculation as fieldname

My preference is the first option, so you don't have to look at the end of a really long line:
``````--example 1:
SELECT	SomeColumnName =	CASE  T1.SUPSTATUS_FW
WHEN '1' THEN 'YES'
ELSE ''
END
FROM	SomeTable

--example 2:
SELECT	CASE  T1.SUPSTATUS_FW
WHEN '1' THEN 'YES'
ELSE ''
END AS SomeColumnName
FROM	SomeTable
``````
0

Author Comment

ID: 39783456
Everyone, all of these were right on... I had the syntax incorrect.  I going to split up the points as everyone provided great info.
0

Author Closing Comment

ID: 39783463
Awarded Aneesh half of the 500 as they were the first one to respond.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.