MSSQL - return column names and value if value is not NULL

Question

In mssql, imagine I have a table with the following data...

id   name   monday   tuesday   wednesday   thursday   friday
1    mark    null    chores      null       gym        swim
2    steve   gym     null        class      hockey     null
3    mike    chores  gym         null       null       null

I would like to have a sql statement, that would return the id, name and day column where the value is not null, for example...

id    name    day        value
1     mark    tuesday    chores
1     mark    thursday   gym
1     mark    friday     swim
2     steve   monday     gym
2     steve   wednesday  class
2     steve   thursday   hockey
2     mike    monday     chores
2     mike    tuesday    gym

Thank you.

You should create table to save week days, and then do a relation between people -- days — Grommy
– Grommy, Commented Jul 22, 2016 at 12:37

Gordon Linoff · Accepted Answer · 2016-07-22 12:37:59Z

3

One method is union all, but I prefer outer apply:

select t.id, t.name, dayname, value
from t outer apply
     (values ('monday', monday),
             ('tuesday', tuesday),
             ('wednesday', wednesday),
             ('thursday', thursday),
             ('friday', friday),
             ('saturday', saturday),
             ('sunday', sunday)
     ) v(dayname, value)
where value is not null;

answered Jul 22, 2016 at 12:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MaxAx Over a year ago

I tried this solution first, and it worked exactly as I wanted. Thank you.

nazark · Accepted Answer · 2016-07-22 12:44:04Z

2

try

select * from yourTable 
unpivot
(
day1 for value in (monday,   tuesday,   wednesday,   thursday,friday) 
) upt

answered Jul 22, 2016 at 12:44

nazark

1,2402 gold badges10 silver badges15 bronze badges

Collectives™ on Stack Overflow

MSSQL - return column names and value if value is not NULL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related