Formatted Output in Stored Procedure

[caterpillar]

Hi All,

I have a stored procedure which displays list of ADMITTED,QUEUED and DISCHARGED patients. The patient status is stored in a table. A represents ADMITTED patient, Q represents QUEUED patient and D represents DISCHARGED patient. I am using a dropdown to display the ADMITTED,QUEUED and DISCHARGED patients respectively. I need to add a new option in the dropdownas ALL which displays all i.e., ADMITTED,QUEUED and DISCHARGED patients but in a classified manner on the same page like:

ADMITTED
--------
RegNo PatientName Age
---- ----------- ---
A1 PNA 70

QUEUED
--------
RegNo PatientName Age
---- ----------- ---
D1 PNQ 53

DISCHARGED
--------
RegNo PatientName Age
---- ----------- ---
Q1 PND 45

How should i code my stored procedure for this kind of formatted output?

Thanks!

[campkev]

post the queries for the 3 lists and I should be able to help you do one query to get the combined list

[caterpillar]

post the queries for the 3 lists and I should be able to help you do one query to get the combined list

Thanks for replying, i am sorry for responding back late. Here goes my Query:

--ADMITTED
SELECT RNO,PNAME,AGE
FROM TAB1
WHERE enroll_status='A'

--QUEUED
SELECT RNO,PNAME,AGE
FROM TAB1
WHERE enroll_status='Q'

--DISCHARGED
SELECT RNO,PNAME,AGE
FROM TAB1
WHERE enroll_status='D'

At present i am making 3 different datatables & calling them separately to be displayed in 3 different datagrids in asp.net. I need to call a single datatable & want to display in 1 datagrid only.

Thanks

[campkev]

does this work for you?

SELECT RNO,PNAME,AGE
FROM TAB1
WHERE enroll_status in ('D','A','Q')

[caterpillar]

Thanks again for your reply. This does not works as i need the output as described in my 1st post!

[pty]

SELECT RNO,PNAME,AGE,'discharged' as STATUS
FROM TAB1
WHERE enroll_status = 'D' 
union
SELECT RNO,PNAME,AGE,'queued' as STATUS
FROM TAB1
WHERE enroll_status = 'Q' 
union
SELECT RNO,PNAME,AGE,'admitted' as STATUS
FROM TAB1
WHERE enroll_status = 'A'