SQL server's case when then statement, sqlcase

Source: Internet
Author: User

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>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.