CrossTab Server查询

Question

我的选择查询：

SELECT      
    COUNT(*) * 500 AS TotalFee, 
    ISNULL(Employee.EFName, '') + ' ' + ISNULL(Employee.EMName, '') + ' ' + ISNULL(Employee.ELName, '') as Name,
    CAST(MONTH(sdatetime) AS int) as Months
FROM            
    Patient_Services 
INNER JOIN
    Employee ON Patient_Services.Doctor_ID = Employee.EmpID
WHERE
    (Patient_Services.S_ID = 1606) 
    AND CAST(MONTH(sdatetime) AS INT) BETWEEN 1 AND 6
GROUP BY 
    CAST(MONTH(sdatetime) AS INT), Employee.EFName, Employee.EMName, Employee.ELName
ORDER BY
    CAST(MONTH(Patient_Services.sdatetime) AS INT)

结果

TotalFee    |       Name        |   Months
38000       |    AKIF DILSHAD   |     1
1500        |    MATEEN AKRAM   |     1
1500        |    AKIF DILSHAD   |     2
2200        |    AKIF DILSHAD   |     3
1500        |    NASERA BHATTI  |     4
500         |    NASERA BHATTI  |     5
500         |    NASERA BHATTI  |     6
12000       |    AKIF DILSHAD   |     6

我想要的结果：

Name           |Jan     |Feb    |Mar    |Apr    |May    |June   |Total  |60%    |40%
AKIF DILSHAD   |38000   |1500   |2200   |0      |0      |12000  |53700  |32220  |21480
MATEEN AKRAM   |1500    |0      |0      |0      |0      |0      |1500   |900    |600
NASERA BHATTI  |0       |0      |0      |1500   |500    |500    |2500   |1500   |1000

请帮助转换我的查询，以获得我想要的结果。

Answer 1

请试试这个：

WITH cte as(
SELECT        COUNT(*)*500 AS TotalFee, ISNULL(Employee.EFName,'') +' '+ ISNULL(Employee.EMName,'') +' '+ ISNULL(Employee.ELName,'') as Name,CAST(MONTH(sdatetime) AS int) as Months
FROM            Patient_Services INNER JOIN
                         Employee ON Patient_Services.Doctor_ID = Employee.EmpID
WHERE        (Patient_Services.S_ID = 1606) AND CAST(MONTH(sdatetime) AS int) Between 1 AND 6
GROUP BY CAST(MONTH(sdatetime) AS int),Employee.EFName, Employee.EMName, Employee.ELName
--order by CAST(MONTH(Patient_Services.sdatetime) AS int)
)

    SELECT [NAME],
        SUM(CASE Months WHEN 1 THEN TotalFee ELSE 0 End) as 'Jan' ,
        SUM(CASE Months WHEN 2 THEN TotalFee ELSE 0 End) as 'Feb' ,
        SUM(CASE Months WHEN 3 THEN TotalFee ELSE 0 End) as 'Mar' ,
        SUM(CASE Months WHEN 4 THEN TotalFee ELSE 0 End) as 'Apr' ,
        SUM(CASE Months WHEN 5 THEN TotalFee ELSE 0 End) as 'May' ,
        SUM(CASE Months WHEN 6 THEN TotalFee ELSE 0 End) as 'Jun' ,
        SUM(TotalFee) AS Total,
        SUM(TotalFee)*.6 as [60%],
        SUM(TotalFee)*.4 as [40%]
      FROM  CTE
      GROUP BY [Name]