August 30th, 2012, 03:10 PM
Rows to columns
I have a couple tables that I am querying to get the below sample data. The name column comes from the Patient_Info column while the test_name, test_date, and result columns come form the Test_Info table. I am joining the 2 tables on the ID field.
Pt_ID Pt_Name Test_name Test_Date Test_Result
1 John Doe Blood 1/1/2012 0.6
1 John Doe Urine 1/1/2012 Negative
2 Jane Doe Serum 12/13/2011 15
2 Jane Doe Blood 12/13/2011 15
2 Jane Doe Serum 10/13/2011 8
So, effectively what I want is this:
SELECT p.Pt_ID, p.Pt_Name, p.Pt_Birth_Date, t.Test_name, t.Test_date, t.Test_result
FROM Patient_Info p
INNER JOIN Test_Info t ON p.ID = t.Pt_ID
1 John Doe Blood 1/1/2012 0.6 Urine 1/1/2012 Negative NULL NULL NULL
2 Jane Doe Serum 12/13/2011 15 Blood 12/13/2011 15 Serum 10/13/2011 8
How do I modify my query to be able to have just one row per patient and have the 'test_name', 'test_date', and 'test_result' as columns of their own. Are there any pre-defined functions that I can use?
1. Not all patients will have the same tests done on them AND
2. This data is refreshed multiple times a day, so the test names may not always be consistently present in the test_info table. (For ex., 'Blood' test may not exist in the table after the SSIS job runs at say, 8AM the next morning)
An example query with dummy data would be really helpful.
(Using SQL Server 2008)
EDIT: I just noticed a thread titled 'Select Problem' by 'Peter85' that is doing something similar to what I am looking for. Only difference is that his columns are fixed. In my case, however, the Test_name column will not always have the same values as shown in the table above.
August 30th, 2012, 04:37 PM
Is self-join the right way to go about this? If yes, can someone show how with an example, please?
August 31st, 2012, 03:58 AM
If you are using SQL 2005 and above you could use the PIVOT function. I only have access to SQL 2000 (no cries of despair please!!) at the moment for the systems that I have developed so I use a number of sub queries for this type of work.
Failing that you could pull the raw data into Excel and then use a Pivot Table to get the data in the format that you require.
August 31st, 2012, 08:28 AM
I looked at the pivot function and wasn't sure if that's the way to go. From my understanding of the function, it seems like the Test_name column in my table should contain a definite list of values. However, that is not the case with my data set. Some times 'Blood', 'Serum', 'Urine' may all co-exist, but at other times, no patients may have a 'Urine' test done at all (meaning that only 'Blood' and 'Serum' are test name values). The reason being that this table is truncated and reloaded multiple times a day with only data for that part of the day. It does not maintain historical data.
Originally Posted by Fear Naught
Conversely, the table could also have newer tests that have never existed before. For ex., when the E-T-L process next loads at say, 12PM, I could have 'Barbiturates' as one of the test names.
part of the query will either have additional values or it'll miss values.
FOR column_name IN (<itemlist>]