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 |
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]')