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


5 comments:

Cool Math 4 Kids 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

Cute Soc 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

Candy Sim said...

The blog or and best that is extremely useful to keep I can share the ideas
of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
gold miner|stick war 3| pokemongo
| stick man|animal jam login

huong duong said...

After all, life is also because you are self-employed, success or failure, forward or also stalled by you only.
download musicas , snapchat baixar , run 3 , b612 , geometry dash apk

Drag Racer V3 said...

Great! i like your post pianotiles2.com