SQL server's case when then statement, sqlcase

SQL server's case when then statement, sqlcase

-- Select DataName,
-- Case Code
--
-- When 'w01' then 'male'
--
-- When 'w07 'then' female'
--
-- Else 'others'
--
-- End as Code
-- From dbo. HistoryData

Select DataName,
(
Case
When Code = 'w01' then 'male'
When Code = 'w07 'then' female'
Else 'others'
End
)
From HistoryData

The access database has SQL server-like case when then statements.

Access does not have case when
However, there is an iif function that can be processed.

SQL Server:
Select bb, cc, case aa when 'one-star level 'then '*'
When 'second-star 'then '**'
When 'three start' then '***'
When 'four-star 'then '****'
When 'five-star 'then '*****'
End
From aaa

Access:
Select bb, cc, iif (aa = 'one start', '*', iif (aa = 'two start', '**', iif (aa = 'three start ', '***', iif (aa = 'four start', '***', iif (aa = 'five start ', '*****') from aaa;
Reference: hi.baidu.com/...a.html

Case when then end usage in MS SQL SERVER

A. Use a select statement with A simple CASE Function

In the SELECT statement, the simple CASE function only checks for equality without performing other comparisons. The following example uses the CASE function to change the display of product series categories to make them easier to understand.

USE AdventureWorks; GOSELECT 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, nameFROM Production. productORDER BY ProductNumber; GO

B. Use the SELECT statement with the CASE search function

In the SELECT statement, the CASE search function allows you to replace the value in the result set based on the comparison value. The following example shows the price as a text comment based on the product price range.

USE AdventureWorks; GOSELECT ProductNumber, Name, 'price range' = case when ListPrice = 0 then' Mfg item-not for resale 'when ListPrice <50 then' Under $ 50' WHEN ListPrice> = 50 and ListPrice <250 then' under $250 'when ListPrice> = 250 and ListPrice <1000 THEN 'under $1000 'else' Over $1000 'ENDFROM Production. productORDER BY ProductNumber; GO

C. Use CASE to replace the IIf function used in Microsoft Access

The CASE function is similar to the IIf function in Microsoft Access. The following example shows a simple query that uses IIf to provide output values for the TelephoneInstructions column in the Access Table named db1.ContactInfo.

SELECT FirstName, Lastname, TelephoneNumber, IIf (IsNull (TelephoneInstructions), "Any time", TelephoneInstructions) AS [When to Contact] FROM db1.ContactInfo

The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in The AdventureWorks view. The view name is Person. vAdditionalContactInfo.

USE AdventureWorksGOSELECT FirstName, Lastname, TelephoneNumber, 'When to contact' = CASE When T ...... the remaining full text>