sql 复杂的行到列的转换

Question

我有以下数据:

DECLARE @TBL TABLE (ID INT, Numb VARCHAR(10),   City VARCHAR(10),   Dates DATE)
INSERT INTO @TBL VALUES 
(101,'S1','AA','2019-07-20'),(101,'S2','BB','2019-05-03'),(101,'S2','AA','2019-07-31'),
(101,'S1','BB','2019-02-02'),(101,'S1','CC','2019-08-02'),(101,'S2','AA','2019-08-09'),
(101,'S2','CC','2019-06-20'),(101,'S3','CC','2019-08-02'),(101,'S2','AA','2019-05-26'),
(101,'S1','CC','2019-01-15'),(101,'S2','AA','2019-07-15'),(101,'S1','AA','2019-04-15'),
(101,'S3','CC','2019-06-05'),(101,'S2','CC','2019-02-14') ,(102,'S3','AA','2019-04-06') ,
(102,'S2','AA','2019-03-09'),(102,'S1','BB','2019-06-11'),(102,'S1','AA','2019-04-08'),
(102,'S3','CC','2019-02-24'),(102,'S2','CC','2019-04-22'),(102,'S3','BB','2019-02-14'),
(102,'S2','AA','2019-04-19')

我编写了一个查询，它根据 Numb 列计算 City 的数量，如下所示:

SELECT distinct
 ID 
, CASE WHEN Numb = 'S1' THEN COUNT(City) END AS 'S1_No'
, CASE WHEN Numb = 'S1' THEN max(Dates) END AS 'S1_Date'
, CASE WHEN Numb = 'S2' THEN COUNT(City) END AS 'S2_No'
, CASE WHEN Numb = 'S2' THEN max(Dates) END AS 'S2_Date'
, CASE WHEN Numb = 'S3' THEN COUNT(City) END AS 'S3_No'
, CASE WHEN Numb = 'S3' THEN max(Dates) END AS 'S3_Date'
FROM @TBL
 GROUP BY ID,Numb

电流输出

期望的输出

我希望所有案例陈述数据根据其 ID 位于一行中。我试过 pivot，但没有运气。有什么办法可以通过查询来编写以获得我想要的输出吗？谢谢

Answer 1

感谢您发布如此易于使用的示例数据。我希望每个人都把它说清楚。您非常接近并且朝着正确的方向前进。只需轻轻一推即可越过终点线。

这会产生您正在寻找的预期输出。

SELECT ID 
    , count(CASE WHEN Numb = 'S1' THEN 1 END) AS S1_No
    , MAX(CASE WHEN Numb = 'S1' THEN Dates END) AS S1_Date
    , count(CASE WHEN Numb = 'S2' THEN 1 END) AS S2_No
    , max(CASE WHEN Numb = 'S2' THEN Dates END) AS S2_Date
    , count(CASE WHEN Numb = 'S3' THEN 1 END) AS S3_No
    , max(CASE WHEN Numb = 'S3' THEN Dates END) AS S3_Date
FROM @TBL
GROUP BY ID