• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4113
  • Last Modified:

SQL 2008 How to specify column name when using case statement

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
66chawger
Asked:
66chawger
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
CASE
      WHEN T1.SUPSTATUS_FW = '0' THEN ''
      WHEN T1.SUPSTATUS_FW = '1' THEN 'YES'
      ELSE ''
END   As NewColumnName ,
0
 
Brian CroweCommented:
CASE T1.SUPSTATUS
   WHEN '1' THEN 'YES'
   ELSE ''
END AS SomeColumnName,
0
 
John_VidmarCommented:
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

Open in new window

0
 
66chawgerAuthor Commented:
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
 
66chawgerAuthor Commented:
Awarded Aneesh half of the 500 as they were the first one to respond.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now