CODEWITHSUNDEEP
sql
Will
Will

Reputation: 8631

sql select a column if

Within SQL is it possible to select a column only if it meets my Criteria?

IF p.Amount != 0.0 
    { select p.Amount from Price p }

This is part of a much larger SQL will be used to filter the column from being displayed if there are values over 0.0

Upvotes: 1

Views: 163

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

If you only want to show values greater than 0.0, you can use a where clause:

select p.amount
from t
where p.amount > 0

If you want non-positive values to be shown, and blanks (NULLs) for the rest, then do:

select (case when p.amount > 0 then p.amount end)
from t

If you only want rows returns when all values are greater then zero, then here is one way:

select p.amount
from (select t.*,
             sum(case when p.amount > 0 then 1 else 0 end) over () as Pgt0
             count(*) over () as cnt
      from t
     ) t
where Pgt0 = cnt

If you actually want to remove the column when, say, all the values are the same, then you need a more complicated expression. SQL SELECT queries define the columns and don't have dynamically configurable columns.

Upvotes: 0

GeorgeVremescu
GeorgeVremescu

Reputation: 1253

Do you want it to not be visible at all? Or just display some other predefined value inside it? Because if you want the column to not appear at all, but only in some cases, that can not be done. However, you can return a different result in the column, should it suffice for you. Use CASE WHEN:

SELECT 
  CASE WHEN p.amount< 700 THEN p.amount
ELSE 0
END
FROM Price p;

Upvotes: 0

Dipali Nagrale
Dipali Nagrale

Reputation: 520

You can use IFF function in SQL Server:

SELECT IIF(p.Amount != 0.0 ) as amount

FROM price p

Upvotes: -1

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

You need to use the CASE statement. Transact-SQL - CASE

USE AdventureWorks2012;
GO
SELECT   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,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Upvotes: 0

paul
paul

Reputation: 22001

SQL criteria are used to select rows, not columns. I have generally found it makes life easier to always select the same columns for a specific query, and later choose whether or not to display them using view logic.

Upvotes: 4

Related Questions