SQL Conditional Column Data Return in a Select Statement

SQL Conditional column data return in a select statement

You didn't mention your DBMS but a searched CASE statement works in all major DBMS's I know off.

SELECT  ID
        , CASE WHEN WorkStream = 'Internal'
               THEN WorkStream
               ELSE Assignee
          END AS Assignee
        , Workstream
FROM    assignees

---

Reference: MSDN

CASE

Evaluates a list of conditions and returns one of multiple possible
result expressions.

SQL Server : Conditional Column on Select query

select 
productname, total, 
case when total < 100 then 'Bad' 
     when total >= 100  and total < 200 then 'OK'
     when total > 200 then 'Good'
end as salestate
from
(       
select 
p.ProductName, sum(sd.Price*sd.Quantity) as Total
from Products p
left join [Sales Details] sd on p.ProductID = sd.ProductID
group by p.ProductName
) t

You can use case statement to assign values based on the sum in the query.

how do I select a column based on condition?

SELECT ordr_num as num, ordr_date as date, 
    CASE WHEN @status<>'Cancelled' THEN ordr_ship_with ELSE NULL END as shipwith 
FROM order 
WHERE ordr_num = @ordrNum

SQL Server : convert column by different condition in select statement

You need to use a Case expression. Assuming the logic above does what you need, this sort of thing should provide the result in the structure you want...

select ta.columna,
       case 
           when CHARINDEX('SHOP',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('SHOP', ta.address)+1)
           when CHARINDEX('LIGHTBOX',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('LIGHTBOX', ta.address)-8)
           when CHARINDEX('ADV',ta.address) > 0 then
               RIGHT(ta.address, charindex('ADV', ta.address)-3)
       end as address
from tablea ta

How to use conditional columns values in the same select statement?

You can't reference a column alias in the select but you can use a CTE as below.

;WITH CTE AS
(
select
  ID_Operation,
  FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end,
  SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end,
  ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end
from 
  AllOperationsTable
)
SELECT *,
       AllChecksOk = Case WHEN 
               (COMPLEX_EXPRESSION_1+ COMPLEX_EXPRESSION_2+ 
               COMPLEX_EXPRESSION_3CHeck = 3) Then 'OK' Else 'No' End
FROM CTE

You can also use CROSS APPLY to define the 3 column aliases then reference them in the main SELECT list as in this example.

SQL Add column with conditional values from another column

select does not change the data in tables. This looks like an use case for a temporary view but unfortunately SQL Server does not support temporary views.

So instead of first creating and filling #tempt with the structure and data from [dbo].[MainTable] then adding three columns to it and then filling them (using update) with calculated values better add and calculate them within the select list of the initial query which creates #tempt. You will have it all done in one go. Here is my suggestion:

select *,
  case when Called in ('Missed', 'No Answer', 'Voicemail', 'Disconnected') then 1 else 0 end as IsColumn,
  case when left(specific_column_a, 2) = '__' then 1 else 0 end as IsTest, 
  case when left(specific_column_b, 2) = '__' then 1 else 0 end as ThisColumn 
into #tempt
from [dbo].[MainTable];

You may see this SO thread too.

How to select rows with conditional values of one column in SQL

You can try using exists -

DEMO

    select * from t t1 where timeline in ('BASELINE','MIDTIME') and
    exists
     (select 1 from t t2 where t1.id=t2.id and timeline in ('BASELINE','MIDTIME')
            group by t2.id having count(distinct timeline)=2)

OUTPUT:

id  timeline
1   BASELINE
1   MIDTIME
2   BASELINE
2   MIDTIME
5   BASELINE
5   MIDTIME

---

Related Topics