Stored Procedure that allows one to search a range of columns and returns an integer value that is stored and ignores if NULL

Question

I have small table with a structure:

DayRecordID     int 
InTime_1    datetime     
Dept1       int  
OutTime_1   datetime     
Reason_1    int  
InTime_2    datetime     
Dept2       int  
OutTime_2   datetime     
Reason_2    int  
InTime_3    datetime     
Dept3       int  
OutTime_3   datetime     
Reason_3    int  
InTime_4    datetime     
Dept4       int  
OutTime_4   datetime     
Reason_4    int  
InTime_5    datetime     
Dept5       int  
OutTime_5   datetime     
Reason_5    int  
InTime_6    datetime     
Dept6       int  
OutTime_6   datetime     
Reason_6    int

I realize this isn't the most ideal Table but before I scrap it and redo, will anyone tell me how to best iterate through the Dept1, Dept2, Dept3, Dept4, Dept5, Dept6 columns and return the VALUES where they are NOT NULL? I don't know if it is the query that is confusing me or the subquery this will be used with.

Thanks!

If you have an opportunity to scrap this table structure and redo it, take advantage of that while you can. This structure will cause you nothing but headaches going forward. — Joe Stefanelli, Dec 10 '14 at 22:04
If you are deadset on keeping this table (I agree 10000% with Joe that you should scrap this) then you will need to evaluate each column. Dept1 IS NOT NULL OR Dept2 IS NOT NULL.... — Sean Lange, Dec 10 '14 at 22:09
Anytime you have to start append numbers to column names, you are almost certainly off the rails relationally. Don't spend anymore time thinking about it, just scrap this bad design immediately. — RBarryYoung, Dec 10 '14 at 22:16
Agree with the preceding comments but what would the desired results look like? — Martin Smith, Dec 10 '14 at 22:35

score 2 · Accepted Answer · edited May 23 '17 at 12:05

As mentioned in the comments, your best bet is to store it in a more unpivoted way. If you already have data in this structure, you can convert what you have to an unpivoted structure using a cross-apply (as the UNPIVOT function won't unpivot multiple columns):

select DayRecordID, Dept, InTime, OutTime, Reason
from tb
cross apply
(
  values 
    (Dept1, InTime_1, OutTime_1, Reason_1),
    (Dept2, InTime_2, OutTime_2, Reason_2),
    (Dept3, InTime_3, OutTime_3, Reason_3),
    (Dept4, InTime_4, OutTime_4, Reason_4),
    (Dept5, InTime_5, OutTime_5, Reason_5),
    (Dept6, InTime_6, OutTime_6, Reason_6)
) c (Dept, InTime, OutTime, Reason)
where Dept IS NOT NULL
 OR InTime IS NOT NULL
 OR OutTime IS NOT NULL
 OR Reason IS NOT NULL;

SQL Fiddle Demo here

(Thanks to @bluefeet for his multi-column unpivot answer here: https://stackoverflow.com/a/18617372/4313829)

Stored Procedure that allows one to search a range of columns and returns an integer value that is stored and ignores if NULL

1 Answers1