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.

UserId INT,
Day4 INt NULL,
Day8 INt NULL,
Day10 INt NULL

INSERT INTO @Table(UserId,
 Day1, Day2, Day3, Day4, Day5, Day6,Day7, Day8,Day9, Day10)
INSERT INTO @Table(UserId,
 Day1, Day2,  Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)

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


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

Unknown 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

alicetaylor said...

I love all the posts, I really enjoyed, I would like more information about this, because it is very nice., Thanks for sharing.

Anonymous said...

I was very impressed by this post, this site has always been pleasant news. Thank you very much for such an interesting post. Keep working, great job! In my free time, I like play game: What about you?

Unknown said...

Thanks for sharing the information. It is very useful for my future. keep sharing

ngocanhng said...

I like your all post. You have done really good work. Thank you for the information you provide, it helped me a lot. I hope to have many more entries or so from you.
Very interesting blog.

harish sharma said...

activa 4g body parts price

jonet said...

It as nearly impossible to find experienced people about this topic, s1288 but you sound like you know what you are talking about! Thanks

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.