Medical software – Gridview – Displaying data from different rows as one row.

For the past two weeks, I've been working on a GUI that allows nurses to enter various measurements about our patients. Basically the database table behind this is 4 huge columns with all the data you need. There’s a lot more, but this took the most time.

 

However, the nurses I work with would prefer the data they enter to look exactly the same as the report that the data will create. Herein lies the rub. So how do you change the data you receive on a grid view so that the several rows you need show on the one row?

 

Create a table on the fly in a stored procedure.

 

You need to create a table on the fly using CREATE TABLE “TableName”, make note of the names you use for this table, as the order of your columns and the names of the columns themselves need to be exactly the same as this table.

 

Now you need to create the rows for your new table.

 

Use WHILE... BEGIN... END loops as a FOR loop for the “rows”.

 

You have to use IF… BEGIN… END branches to cover all the values in your rows that could become NULLS. This is so the null columns actually return a column for your table to read, or else that row will not appear.

 

Now for the rows columns themselves:

 

Basically, what you want to do is group your rows and sort them using TOP and the ORDER BY commands in your SELECT query. Now you’ve got you rows that you can choose. Use another select command to get your row, and make sure to use the AS command to set the columns from that row to be the same name as one of the columns in your newly created table.

 

Here’s some code that should help you understand:

 

Select * from

(

Select rowchoose.Description as Measure, rowchoose.Measure as Unit, rowchoose.Measurement_id as mIDB, rowchoose.Value as Baseline_Value, rowchoose.Date_of_test as Baseline_Date, rowchoose.Measurement_Type as mTypeB FROM (

SELECT TOP 1 * FROM (

SELECT   row_number () over (order by Measurement.Date_Of_Test) as rn, Measurement.Measurement_id, Measurement.Patient, Measurement.Date_of_Test, Measurement.Value, Measurement.Measurement_Type,

                      Measurement_Type.Measurement_Type_id, Measurement_Type.Description, Measurement_Type.Measure, Patient.Patient_ID, Patient.Patient_First_Name,

                      Patient.Patient_Last_Name, Patient.DOB

FROM         Measurement INNER JOIN

                      Measurement_Type ON Measurement.Measurement_Type = Measurement_Type.Measurement_Type_id INNER JOIN

                      Patient ON Measurement.Patient = Patient.Patient_id

WHERE     (Measurement.Measurement_Type = @Measurement_type) AND (Patient.Patient_ID = @patient_id) AND (Measurement.Baseline = 1)

) x

ORDER BY x.Date_Of_Test

) rowchoose

WHERE rowchoose.rn = 1

) rowbase,

(

Select rowchoose.Measurement_id as mID1, rowchoose.Value as Q1_Value, rowchoose.Date_of_test as Q1_Date, rowchoose.Measurement_Type as mType1 FROM (

SELECT TOP 4 * FROM (

SELECT   row_number () over (order by Measurement.Date_Of_Test) as rn, Measurement.Measurement_id, Measurement.Patient, Measurement.Date_of_Test, Measurement.Value, Measurement.Measurement_Type,

                      Measurement_Type.Measurement_Type_id, Measurement_Type.Description, Patient.Patient_ID, Patient.Patient_First_Name,

                      Patient.Patient_Last_Name, Patient.DOB

FROM         Measurement INNER JOIN

                      Measurement_Type ON Measurement.Measurement_Type = Measurement_Type.Measurement_Type_id INNER JOIN

                      Patient ON Measurement.Patient = Patient.Patient_id

WHERE     (Measurement.Measurement_Type = @Measurement_type) AND (Patient.Patient_ID = @patient_Id ) AND (Measurement.Baseline = 0)

) x

ORDER BY x.Date_Of_Test

) rowchoose

WHERE rowchoose.rn = 1

) row1

 

I get the TOP 4 rows and get either baseline values or non-baseline values, then select the top row using row_number() to get the top row. Then I get the values I need for that row and use AS to assign that row to the correct columns in the overall table.

 

When it's all said and done, you then finally select * from the table you created (which returns the table with all columns and all rows) then drop the table. You need to drop the table so the stored procedure can run again.

 

Hope that helps, if you have any questions, feel free to leave a comment.