Awais Sharafat Awais Sharafat - 27 days ago 9
ASP.NET (C#) Question

Sql Cross Tab (Query)

My Query is:

SELECT
Department.Dept_Name,
Patient.Patient_Type,
COUNT(*) AS TotalPatients
FROM Patient
INNER JOIN Payment
ON Patient.RegNo = Payment.RegNo
INNER JOIN Department
ON Payment.DeptID = Department.Dept_ID
WHERE (CONVERT(varchar, Patient.RegDateTime, 112) = CONVERT(varchar, GETDATE()-30, 112))
GROUP BY
Department.Dept_Name,
Patient.Patient_Type


Result of this query is:

Dept_Name | Patient_Type | TotalPatients
ACCIDENT & EMERGENCY | Entitled | 5
MCH | Entitled | 4
ACCIDENT & EMERGENCY | General | 410
BURN CARE CENTER | General | 5
G-MEDICINE (CH) | General | 20
G-SURGERY (CH) | General | 12
MCH | General | 39
ACCIDENT & EMERGENCY | Staff | 4
G-MEDICINE (CH) | Staff | 1
BURN CARE CENTER | Referred | 1


Result wanted:

Dept_Name | Entitled | General | Staff | Referred |
ACCIDENT & EMERGENCY | 5 | 410 | 4 | 0 |
MCH | 4 | 39 | 0 | 0 |
BURN CARE CENTER | 0 | 5 | 0 | 1 |
G-MEDICINE (CH) | 0 | 20 | 1 | 0 |
G-SURGERY (CH) | 0 | 12 | 0 | 0 |


Please Help me to get my desire result.
Departments and Types can be increased or decreased.

Answer

I tested the below with your provided sample data and it returns accordingly. Basically for each column you want to conditionally pass in the TotalPatients tot he SUM aggregator based on the PatientType value.

select
   Dept_Name
  ,sum(case when Patient_Type = 'Entitled' then TotalPatients ELSE 0 end) Entitled 
  ,sum(case when Patient_Type = 'General'  then TotalPatients ELSE 0 end) General   
  ,sum(case when Patient_Type = 'Staff'    then TotalPatients ELSE 0 end) Staff 
  ,sum(case when Patient_Type = 'Referred' then TotalPatients ELSE 0 end) Referred 
from @t
group by Dept_Name

In the cases where you need dynamic columns...

IF EXISTS (SELECT * 
       FROM   tempdb.dbo.sysobjects o 
       WHERE  o.xtype IN ( 'U' ) 
              AND o.id = Object_id(N'tempdb..#t')) 
  DROP TABLE #t; 

CREATE TABLE #t 
  ( 
  dept_name     NVARCHAR(255), 
 patient_type  NVARCHAR(255), 
 totalpatients INT 
  ) 

INSERT INTO #t 
        (dept_name, 
         patient_type, 
         totalpatients) 
Insert Into #T (Dept_Name, Patient_Type  , TotalPatients) 
SELECT department.dept_name,  
   patient.patient_type,  
   Count(*) AS TotalPatients  
FROM   patient  
   INNER JOIN payment  
           ON patient.regno = payment.regno  
   INNER JOIN department  
           ON payment.deptid = department.dept_id  
WHERE  ( CONVERT(VARCHAR, patient.regdatetime, 112) =  
            CONVERT(VARCHAR, Getdate() - 30, 112) )  
GROUP  BY department.dept_name,  
      patient.patient_type 

DECLARE @cols  AS NVARCHAR(max), 
    @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename(patient_type) 
                  FROM   #t 
                  GROUP  BY dept_name, 
                            patient_type 
                  ORDER  BY 1 
                  FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
           '') 

SET @query = N'SELECT Dept_Name, ' + @cols + N' 
         from               
         #T x             
         pivot              
           (                     
            SUM(TotalPatients)                 
            for Patient_Type in (' 
            + @cols + N')             
            ) p ' 

EXEC Sp_executesql @query; 
Comments