At least one column contains matching value

Result should be A and C , no?

DECLARE @Table TABLE

(

Id CHAR(1),

Day1 INT NULL,

Day2 INT NULL,

Day3 INT NULL,

Day4 INt NULL



);



INSERT INTO @Table(Id, Day1, Day2, Day3, Day4)

VALUES('A',10,0,20,3);

INSERT INTO @Table(Id, Day1, Day2, Day3, Day4)

VALUES('B',0,0,0,0);

INSERT INTO @Table(Id, Day1, Day2, Day3, Day4)

VALUES('C',50,25,15,5);



SELECT * FROM

(

SELECT Id, DayQuantity AS DayMax FROM @Table

UNPIVOT (DayQuantity FOR DayNumber IN (Day1,Day2,Day3,Day4)) AS c

) AS der WHERE DayMax>0

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/



MS SQL optimization: MS SQL Development and Optimization

MS SQL Blog: Large scale of database and data cleansing

Remote DBA Services: Improves MS SQL Database Performance