How do I return values from multiple columns when the column names are based on a variable result [closed]

I have a table in SQL Server which lists dates for different stages in a journey in separate columns. I also have a column which shows the current stage of the journey.

For each stage, there is a column header denoting the stage name, and in the column will be the date associated to that stage.

I want to run a SELECT statement that will match the ‘current stage’ to a column name and return the date associated.

For example this could be the table (the actual table has 40 stages – don’t ask!):

record id | current stage      | met client | contract agreed | service completed | on hold**
   11111S | met client         | 2019-01-02 | NULL            | NULL              | NULL
   22222P | contract agreed    | 2019-01-02 | 2019-01-20      | NULL              | NULL
   33333A | on hold            | 2019-01-02 | 2019-01-20      | NULL              | 2019-02-10
   44444C | service completed  | 2019-01-02 | 2019-01-20      | 2019-03-01        | 2019-02-10

I would like to write a SELECT statement that would show:

record_id | current_stage     | date_of_current_stage
   11111S | met client        | 2019-01-02 |
   22222P | contract agreed   | 2019-01-20 |
   33333A | on hold           | 2019-02-10 |
   44444C | service completed | 2019-03-01 |

Answer

The following query using a dynamic UNPIVOT operation will do the work:

CREATE TABLE #yourTable ( [record id] INT,[current stage] VARCHAR(255), [met client] DATE, [contract agreed] DATE, [service completed] DATE, [on hold] DATE)

INSERT INTO #yourTable VALUES 
(11111, 'met client', '2019-01-02', NULL, NULL, NULL),
(22222, 'contract agreed', '2019-01-02', '2019-01-20', NULL, NULL),
(33333, 'on hold', '2019-01-02', '2019-01-20', NULL, '2019-02-10'),
(44444, 'service completed', '2019-01-02', '2019-01-20', '2019-03-01', '2019-02-10')


DECLARE @col NVARCHAR(MAX) = '';
SELECT @col += ',' + QUOTENAME([current stage]) FROM #yourTable
SET @col = STUFF(@col,1,1,'')

EXEC ( 'SELECT unpiv.[record id], unpiv.[current stage], [Date] AS [Date_of_current_stage] FROM #yourTable UNPIVOT ([Date] FOR [Stage] IN ('+@col+') ) unpiv WHERE [current stage] = [Stage]')