SQL多列Pivot需求：在现有Pivot中新增Date_input列

我有如下的Mytable表：

我当前用以下SQL实现ID列的透视：

select no, br, [1] as ID1, [2] as ID2, [3] as ID3
from (select TT, id, no, Br from Mytable) Table_Pivot
pivot
(min(ID) for TT in ([1] , [2] , [3])) bang

现在需要修改该语句，在透视结果中同时加入Date_input列，得到如下格式的结果：

方法一：CASE表达式+GROUP BY（推荐） #

通过CASE表达式分别匹配不同TT值，提取对应ID和日期，再按No和Br分组聚合，逻辑直观且性能更优：

SELECT
    no AS NoB,
    br,
    MAX(CASE WHEN TT = 1 THEN ID END) AS ID1,
    MAX(CASE WHEN TT = 1 THEN Date_input END) AS Date_input1,
    MAX(CASE WHEN TT = 2 THEN ID END) AS ID2,
    MAX(CASE WHEN TT = 2 THEN Date_input END) AS Date_input2,
    MAX(CASE WHEN TT = 3 THEN ID END) AS ID3,
    MAX(CASE WHEN TT = 3 THEN Date_input END) AS Date_input3
FROM Mytable
GROUP BY no, br
ORDER BY no;

方法二：多次透视后关联 #

先分别对ID和Date_input做透视，再通过No字段关联结果：

WITH PivotID AS (
    SELECT no, br, [1] as ID1, [2] as ID2, [3] as ID3
    FROM (SELECT TT, id, no, Br FROM Mytable) Table_Pivot
    PIVOT (MIN(ID) FOR TT IN ([1], [2], [3])) AS PivotID
),
PivotDate AS (
    SELECT no, [1] as Date_input1, [2] as Date_input2, [3] as Date_input3
    FROM (SELECT TT, Date_input, no FROM Mytable) Table_Pivot
    PIVOT (MIN(Date_input) FOR TT IN ([1], [2], [3])) AS PivotDate
)
SELECT 
    p1.no AS NoB, 
    p1.br, 
    p1.ID1, 
    p2.Date_input1, 
    p1.ID2, 
    p2.Date_input2, 
    p1.ID3, 
    p2.Date_input3
FROM PivotID p1
JOIN PivotDate p2 ON p1.no = p2.no
ORDER BY p1.no;

两种方法都能得到目标结果，方法一无需子查询和关联，更简洁高效。

内容的提问来源于stack exchange，提问作者Lucio