时间戳

首页 > 解决方案 > sql 复杂的行到列的转换

sql - sql 复杂的行到列的转换

问题描述

我有以下数据：

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

我写了一个查询，计算City基于列的基数，Numb如下所示：

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 在一行中。我试过枢轴，但没有运气。有什么方法可以通过查询编写来获得我想要的输出？谢谢

标签： sqlsql-servertsql

解决方案

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

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

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

推荐阅读