Unpivot rows into columns

I want to unpivot the a table, based on Case number: (see image for the formatted table)

However, it is a large dataset, so I cannot put the value of every date in the unpivot function. Any help would be greatly appreciated!

Answer

I recommend using cross apply for this:

select c.case_number, v.[date], v.status
from cases c cross apply
     (values (open, 'open'), (approved, 'approved')
     ) v([date], status)
order by c.case_number, v.[date];

Technically, apply implements a “lateral join”. This is a very powerful mechanism, and unpivoting is a good introduction to what they do.

By contrast, unpivot is bespoke syntax available in only a few databases. It only does one thing — and that one thing can just as easily be done in an other way.