Hi friends
I had recently a client who has a table with more than 90's column and his requirement was to return the all columns that IS NOT NULL. So starting with sample T-SQL you will need to filter each column to check for NULLs, like WHERE col1 IS NOT NULL or col2 IS NOT NULL... or perhaps even using an aggregation to eliminate NULLs. But I found pretty nice solution, so take a look at below DDL. We have a table with number of columns (Dayn) to be checked for NULLs. I used simple UNPIVOT output of multiple rows into multiple columns in a single row.
DECLARE @Table TABLE
(
UserId INT,
Day1 INT NULL,
Day2 INT NULL,
Day3 INT NULL,
Day4 INt NULL,
Day5 INT NULL,
Day6 INT NULL,
Day7 INT NULL,
Day8 INt NULL,
Day9 INT NULL,
Day10 INt NULL
);
INSERT INTO @Table(UserId,
Day1, Day2, Day3, Day4, Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(1,null,null,20,3,null,null,null,null,null,null);
INSERT INTO @Table(UserId,
Day1, Day2, Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(2,50,25,15,5,null,null,null,null,null,null);
SELECT UserId, Col_NotNull FROM @Table
UNPIVOT (Col_NotNull FOR DayNumber
IN (Day1, Day2, Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)) AS c
UserId Col_NotNull
1 20
1 3
2 50
2 25
2 15
2 5
