Friday, May 10, 2013

One more method of using UNPIVOT command


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


4 comments:

Anonymous said...

With me... very very difficult...
I want to have solution
Run 3
Head Soccer | Juegos De Terror |
Mahjong Free Games | Cool math 4 kids

Unknown said...

This is the selected site free games and play great. Same goes for your play.
gun mayhem
tank trouble 2
learn to fly 2
can your pet 2
happy wheels game

Drag Racer V3 said...

Great! i like your post pianotiles2.com

Sophie Grace said...

Really, your answer is so appreciative to me because I also agree to you that above info need to food storage. According to your instructions, I will follow it regularly. In this game sims 4 skill cheats you can fitness, it's available on Xbox one.