Sql query row value if exists

Question

Sql query row value if exists

ShawnBryan

SSCrazy

Points: 2157
More actions
July 24, 2018 at 12:17 pm

#373803

Dear All,
i would like to create the virtual column result with if exist condition.
Example : If column1 value is equal to N1 then i would like to create virtual column with value "Antony"
How to achieve this please advise.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

Select Column1, VirtualColumn = iif(Column1 = 'N1', 'Antony', NULL)
From Table

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76988 More actions · Answer 2

ShawnBryan - Tuesday, July 24, 2018 12:17 PM
Dear All,
i would like to create the virtual column result with if exist condition.
Example : If column1 value is equal to N1 then i would like to create virtual column with value "Antony"
How to achieve this please advise.
Thanks.

They're called computed columns in T-SQL. What you are trying to do isn't possible. Here is the relevant part of the documentation for Computed Columns:

computed_column_expression
Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or include an alias data type.

Note: I've bolded the most relevant section

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

drew.allen - Tuesday, July 24, 2018 12:34 PM
ShawnBryan - Tuesday, July 24, 2018 12:17 PM
Dear All,
i would like to create the virtual column result with if exist condition.
Example : If column1 value is equal to N1 then i would like to create virtual column with value "Antony"
How to achieve this please advise.
Thanks.
They're called computed columns in T-SQL. What you are trying to do isn't possible. Here is the relevant part of the documentation for Computed Columns:
computed_column_expression
Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or include an alias data type.

Note: I've bolded the most relevant section

Aha, is that what he meant? 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ShawnBryan SSCrazy Points: 2157 More actions · Answer 4

Dear Phil, It works,

Select twenty, Route_Name = iif(twenty = '220', 'Antony',Null), Route_Name = iif(twenty = '20', 'Bony',null)
FROM [d].[dbo].[t4]
But i want to show it one line(Virtual Column) instead of two route name , expected.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ShawnBryan - Tuesday, July 24, 2018 3:21 PM
Dear Phil, It works,
Select twenty, Route_Name = iif(twenty = '220', 'Antony',Null), Route_Name = iif(twenty = '20', 'Bony',null)
FROM [d].[dbo].[t4]
But i want to show it one line(Virtual Column) instead of two route name , expected.

Try this:
Select twenty , Route_Name = case twenty when '220' then 'Antony' when '20' then 'Bony' else null end; FROM [d].[dbo].[t4]

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Lynn Pettis - Tuesday, July 24, 2018 3:29 PM
ShawnBryan - Tuesday, July 24, 2018 3:21 PM
Dear Phil, It works,
Select twenty, Route_Name = iif(twenty = '220', 'Antony',Null), Route_Name = iif(twenty = '20', 'Bony',null)
FROM [d].[dbo].[t4]
But i want to show it one line(Virtual Column) instead of two route name , expected.
Try this:
Select twenty , Route_Name = case twenty when '220' then 'Antony' when '20' then 'Bony' else null end; FROM [d].[dbo].[t4]

Lynn's code can be expanded as needed, but eventually, you may want to place that "data relationship" into a table and use that table in a LEFT OUTER JOIN instead of using a CASE statement. Also, when you spoke of a "virtual" column, that really doesn't apply here, as there's nothing virtual about the RouteName column. It's a real column that is created as a result of a CASE statement as opposed to being a column from a table. In a manner of speaking, you could call it "computed", but that might get confused with actual computed columns. Derived column might be the best term.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)