CrossTab SQL Server query

Question

My select query:

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)

Result

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

My desired results:

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

Please help to convert my query to get my desired result.

You should use [Pivot](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — neer, Nov 05 '16 at 06:31
The `MONTH()` function **already** returns an `INT` - there's absolutely no reason and no point in casting that to an `INT` explicitly ... — marc_s, Nov 05 '16 at 08:06

score 0 · Accepted Answer · answered Nov 05 '16 at 07:03

Please try this:

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]

I want to ask you that view only those month which pass in where condition. If I pass startmonth = 2 and endMonth = 7 than only show 2 to 7 months. — Awais Sharafat, Nov 05 '16 at 18:20
if you pass 2 to 7, then Jan will show zero and July will not show as separate month but will be included in the Totals columns. In order to show any month, you either copy the SUM/CASE statement for all the 12 months or convert the query to become dynamic query. for Dynamic query, please ready about Dynamic PIVOT. — Ahmed Saeed, Nov 05 '16 at 18:52

CrossTab SQL Server query

1 Answers1