CODEWITHSUNDEEP
sqlsql-server
fracture
fracture

Reputation: 141

How do I add a conditional column to a union query in MS SQL?

In MS SQL is there a way I can add a conditional column to a union query like the one below based on the value of the first column?

basically im trying to add the bin value as a fourth column if the part value is like ('%XX') or have it display 0, so far I have only been able to add another column which mirrors the MAX(part_bins.bin) column if the code doesn't meet the like.

SELECT part_bins.part AS part,
       SUM(part_bins.free_stock) AS FREE_STOCK,
       MAX(PART_BINS.BIN) AS Bin
FROM part_bins
LEFT OUTER JOIN part ON part_bins.part = part.part
WHERE (part_bins.free_stock > 0)
GROUP BY part_bins.part
UNION
SELECT part.master_part AS part,
       SUM(part_bins.free_stock) AS FREE_STOCK,
       MAX(PART_BINS.BIN) AS Bin
FROM part_bins
LEFT OUTER JOIN part ON part_bins.part = part.part
WHERE (part_bins.free_stock > 0)
GROUP BY part.master_part

Upvotes: 0

Views: 1043

Answers (1)

Kaf
Kaf

Reputation: 33809

You can use a CASE statement and you have to add it in both queries to add the fourth column like;

--4th Column for both queries, if bin_value is a numeric type
case when part_value like '%XX' then bin_value 
       else 0 end as fourth_column

--4th Column for both queries, if bin_value is a string type
case when part_value like '%XX' then bin_value 
       else '0' end as fourth_column

Upvotes: 1

Related Questions