Thread: Rows to columns

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    30
    Rep Power
    6

    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
    ------------------------------

    Code:
    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
    So, effectively what I want is this:
    ------------------------------
    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?

    Note that:
    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    30
    Rep Power
    6
    Is self-join the right way to go about this? If yes, can someone show how with an example, please?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Folkestone, Kent, UK
    Posts
    3
    Rep Power
    0
    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.

    HTH
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    30
    Rep Power
    6
    Originally Posted by Fear Naught
    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.

    HTH
    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.

    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.
    Therefore, the
    Code:
    FOR column_name IN (<itemlist>]
    part of the query will either have additional values or it'll miss values.

    Suggestions?

IMN logo majestic logo threadwatch logo seochat tools logo