Unpivot T-SQL query with manual column join

Question

I'm trying to minimize the code lines for this:

Table:

Date1      Date2      Date3
12/03/2019 13/05/2019 01/03/2020
04/05/2018 17/06/2019 07/04/2020

....

From here, I'm writing a query with unions that looks like this:

select 'Date1' as ColumnName,
       'This date is for something1' as ColumnMeaning
        max(Date1) as ColumnMax
from tableName
union
select 'Date2' as ColumnName,
       'This date is for something2' as ColumnMeaning
        max(Date2) as ColumnMax
from tableName
union
select 'Date3' as ColumnName,
       'This date is for something3' as ColumnMeaning
        max(Date3) as ColumnMax
from tableName

I want to minimize this code as there are around 30 date columns and this makes the code pointlessly bulky. I've tried unpivot operator but the problem is, I am not able to replicate the ColumnMeaning column.

Is there any way to effectively achieve this?

Answer 1

One alternative would be apply :

select dates, colname, colsomething, max(dates) as colmax
from table t cross apply
     ( values (t.date1, 'date1', 'This date is for something1'),
               . . .
              (t.date30, 'date130', 'This date is for something30')
     ) tt(dates, colname, colsomething);
group by colname, colsomething;