sql Complex Row to column transformation

Question

I have the data below:

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

I wrote a query which counts the number of City base on Numb column as follows:

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

current output

desired output

I want all the case statement data to be in a single row based on their ID. I tried pivot, yet no luck. Is there any way I can write by query to get my desired output? Thanks

Answer 1

Thank you for posting such easily consumable sample data. I wish everybody would make it so clear. You were super close and heading in the right direction. Just needed a little nudge to cross the finish line.

This produces the expected output you are looking for.

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