Sorting of Multiple Date columns in SQL Server result set

Question

I have formed a result set with 6 different date columns like the below.

I am trying to achieve the result set like this. I have tried with the below code but it was not working perfectly.

The following things should be considered for the final result set.

If all the 6 dates are same for a particular model then we can show it in single row.
Dates should be in sorted order. lets say for row number 2 we have three dates. so we should display it in three rows under its corresponding column consider that the final complete result set would be in a sorted manner.

Here is my code.

DECLARE @Input table (year int,
                      fmy char(2),
                      model char(6),
                      d1 date,
                      d2 date,
                      d3 date,
                      d4 date,
                      d5 date,
                      d6 date);

INSERT INTO @Input
SELECT T.year,
       T.fmy,
       T.model,
       CONVERT(date,T.d1,101),
       CONVERT(date,T.d2,101),
       CONVERT(date,T.d3,101),
       CONVERT(date,T.d4,101),
       CONVERT(date,T.d5,101),
       CONVERT(date,T.d6,101)
FROM (VALUES (2019, 'DD', 'FCRAC2', '1/1/2018', '1/1/2018', '1/1/2018', '1/1/2018', '1/1/2018', '1/1/2018'),
             (2019, 'DD', 'FCRAC2', '07/10/2018', NULL, '1/8/2019', '3/1/2018', NULL, NULL),
             (2019, 'DD', 'FCRAC2', '9/5/2018', NULL, NULL, '7/3/2018', NULL, NULL),
             (2019, 'DD', 'FCRAC2', '1/8/2019', NULL, NULL, '1/8/2019', NULL, NULL)--,
             --(2019, 'DD', 'FCRAC2', '9/5/2018', NULL, NULL, NULL, NULL, NULL)
             ) AS T (year, fmy, model, d1, d2, d3, d4, d5, d6);
SELECT * FROM @Input;

WITH Dates AS(
    SELECT DISTINCT D
    FROM @Input
         CROSS APPLY (VALUES(d1),(d2),(d3),(d4),(d5),(d6)) V(D))
select * from Dates

;WITH Dates AS(
    SELECT DISTINCT D
    FROM @Input
         CROSS APPLY (VALUES(d1),(d2),(d3),(d4),(d5),(d6)) V(D))
SELECT I.[year],
       I.fmy,
       I.model,
       CASE D.D WHEN I.d1 THEN I.d1 END AS d1,
       CASE D.D WHEN I.d2 THEN I.d2 END AS d2,
       CASE D.D WHEN I.d3 THEN I.d3 END AS d3,
       CASE D.D WHEN I.d4 THEN I.d4 END AS d4,
       CASE D.D WHEN I.d5 THEN I.d5 END AS d5,
       CASE D.D WHEN I.d6 THEN I.d6 END AS d6
FROM Dates D
     LEFT JOIN @Input I ON (D.D = I.D1 or D.D =I.D2 or D.D =I.D3 or D.D =I.D4 or D.D = I.D5 or D.D =I.D6)
WHERE D.D is not null
ORDER BY D.D;

user_185647 · Answer

the trick is to include D in the GROUP BY clause, then you will get each distinct D per row

SELECT  year, fmy, model,
        MAX(CASE WHEN D = D1 THEN D END) AS D1,
        MAX(CASE WHEN D = D2 THEN D END) AS D2,
        MAX(CASE WHEN D = D3 THEN D END) AS D3,
        MAX(CASE WHEN D = D4 THEN D END) AS D4,
        MAX(CASE WHEN D = D5 THEN D END) AS D5,
        MAX(CASE WHEN D = D6 THEN D END) AS D6
FROM    @Input
        CROSS APPLY 
        (
            VALUES(D1),(D2),(D3),(D4),(D5),(D6)
        ) V(D)
WHERE   V.D IS NOT NULL
GROUP BY year, fmy, model, D

Sorting of Multiple Date columns in SQL Server result set

1 Answers