Click here to Skip to main content
14,300,422 members
Rate this:
Please Sign up or sign in to vote.
See more:
Dear Sir,
 I am using simple query it result in a grid view.
But I require in Tabular Form ....
      Date from 01/08/2019 to 31/08/2019
Department                      Female Male 
ANESTHESIOLGIST & INVESTIGATION 15      10
GYNAECOLOGY                     1         

Please give me solution.


What I have tried:

I am using ASP .Net and MS SQL.
Result from a query is: 
select Adm_date,Department_Name,P_Gender, count(*) as Num from Vw_IPDPatDetails where DischageStatus='Y' and (E_date between '8/1/2019' and '8/31/2019') group by Adm_date,P_Gender,Department_Name
Date  Department                      Sex Count
01/08/2019 ANESTHESIOLGIST & INVESTIGATION Female 3
01/08/2019 ANESTHESIOLGIST & INVESTIGATION Male 5
02/08/2019 ANESTHESIOLGIST & INVESTIGATION Female 7
02/08/2019 GYNAECOLOGY                     Female 1
02/08/2019 ANESTHESIOLGIST & INVESTIGATION Male 5 
120 line ...
Posted
Updated 10-Sep-19 1:47am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

All you have to do is to Pivot[^] data.

This should do the trick:
SELECT Department, [Male], [Female]
FROM (
    SELECT Department_Name As Department, P_Gender
    FROM Vw_IPDPatDetails
    WHERE DischageStatus='Y' AND (E_date between '8/1/2019' AND '8/31/2019')
) AS DT
PIVOT(COUNT(Department) FOR P_Gender IN ([Male], [Female])) AS PT
   
Comments
LebneizTech 10-Sep-19 8:01am
   
Sir, this error:
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Maciej Los 10-Sep-19 8:07am
   
LebneizTech 10-Sep-19 9:35am
   
Now error shows:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Department'.
Maciej Los 10-Sep-19 9:37am
   
Please, change it to [Department] and let me know.
LebneizTech 10-Sep-19 9:57am
   
I had Changed, but not working sir.
Maciej Los 11-Sep-19 2:30am
   
So, check the name/alias of column. Note, that i have no access to your data and can't read direct from your screen. A pivot statement is correct.
LebneizTech 11-Sep-19 9:26am
   
This is running Sir,
SELECT *
FROM (SELECT Department_Name as Department, P_Gender,count(*) as CC
FROM Vw_IPDPatDetails
WHERE DischageStatus='Y' AND (E_date between '8/1/2019' AND '8/31/2019') group by Department_Name,P_Gender) as DT
PIVOT(SUM(CC)
FOR P_Gender IN ([Male],[Female])) AS PVTTable
Maciej Los 11-Sep-19 9:35am
   
Great!
I'm proud of you.
Cheers!
Maciej
Rate this:
Please Sign up or sign in to vote.

Solution 2

   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100