How do I display list data into tabular form

Question

0.00/5 (No votes)

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 10-Sep-19 1:06am

LebneizTech

Updated 10-Sep-19 1:47am

v2

Add a Solution

2 solutions

Solution 2

See article here: Dynamically Bind FormView Control In ASP.NET From Database - Part Three[^]

Posted 10-Sep-19 1:47am

RickZeeland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2019-09-10T01:44:00

Solution 1

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

This should do the trick:

SQL

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

Posted 10-Sep-19 1:44am

Maciej Los

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

To resolve issue with "compatibility level", check this: sql server - Why Does UNPIVOT Work on Compatibility Level 80 DB? - Database Administrators Stack Exchange[^]

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