问作为新列的不同值&计数

EN

select new_table.model, count (new_table.model) as drives, sum (new_table.distance) as distance, 
       sum(case when driver = 'john' then 1 else 0 end) as john,
       sum(case when driver = 'juliet' then 1 else 0 end) as juliet,
       sum(case when driver = 'borat' then 1 else 0 end) as borat
from new_table
group by model

CREATE TABLE DBO.TEST_TABLE (ID INT,MODEL CHAR(1),DATETIME VARCHAR(255),DRIVER VARCHAR(255),DISTANCE INT)

INSERT INTO DBO.TEST_TABLE
VALUES
 (1,'S','04/03/2009','JOHN',399)
,(2,'X','04/03/2009','JULIET',244)
,(3,'3','04/03/2009','BORAT',555)
,(4,'3','03/03/2009','JOHN',300)
,(5,'X','03/03/2009','JULIET',200)
,(6,'X','03/03/2009','BORAT',500)
,(7,'S','24/12/2008','BORAT',600)
,(8,'X','01/01/2009','BORAT',700)


Declare @Query_ nvarchar(MAX)
Declare @Cols_For_Pivot_ nvarchar(MAX) 


SELECT @Cols_For_Pivot_= COALESCE(@Cols_For_Pivot_ + ',','') + QUOTENAME(DRIVER)
FROM (SELECT DISTINCT DRIVER FROM DBO.TEST_TABLE) AS PivotTable

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP
SET   @Query_ = 
    N'SELECT DISTINCT
             MODEL
            ,COUNT(DATETIME) OVER(PARTITION BY MODEL)   AS DRIVES
            ,SUM(DISTANCE) OVER(PARTITION BY MODEL)  AS DISTANCE
            , ' +   @Cols_For_Pivot_ + '
    INTO #TEMP
    FROM DBO.TEST_TABLE
    PIVOT(COUNT(DRIVER) 
          FOR DRIVER IN (' + @Cols_For_Pivot_ + ')) AS P'

EXEC sp_executesql @Query_