SQL PIVOT(?) query

Question

I have spent a full day on this and just don't get it. Usually I am pretty good with this stuff but I am just getting back in to SQL and just cannot figure this out. Any help you can provide would be very much appreciated.

I am running SQL Server 2008 R2. I have a database where I keep patient data (no names, just identifiers) and clinical test results. I have put together a view to query the various data tables and collect the information I need into one spot. The results of my query give me a table like this: (Tests and values are made up - I know nothing about the tests. I'm just the tech - lol.)

PtName   |  Age  |  Race  |  Ethnicity    |  DrawDate   |  TestType   | TestResult
PT001    |  17   |  White |  Non-Hispanic |  05/17/2011 |  WBC        | 6.8
PT001    |  17   |  White |  Non-Hispanic |  05/17/2011 |  HGB        | 14.1
PT001    |  17   |  White |  Non-Hispanic |  05/17/2011 |  Platelets  | 142.0
PT001    |  17   |  White |  Non-Hispanic |  05/29/2011 |  WBC        | 7.2
PT002    |  34   |  White |  Hispanic     |  05/17/2011 |  WBC        | 7.8
PT002    |  34   |  White |  Hispanic     |  05/17/2011 |  Platelets  | 255.0

What I want is:

PtName   |  Age  |  Race  |  Ethnicity    |  DrawDate   |  WBC  |  HGB  |  Platelets
PT001    |  17   |  White |  Non-Hispanic |  05/17/2011 |  6.8  |  14.1 |  142.0
PT001    |  17   |  White |  Non-Hispanic |  05/29/2011 |  7.2  |       |     
PT002    |  34   |  White |  Hispanic     |  05/17/2011 |       |       |  255.0

I worked out a PIVOT query (first one - very proud, didn't work - not so proud) but then realized that using the aggregate in the PIVOT query would not give me multiple visits for each patient.

I am dealing with hundreds of patients, some have multiple visits, with up to eight tests each. Is a PIVOT query the best way to deal with this? Is there a better way? Some form of subquery perhaps? I am willing to try about anything.

Can you show the tables (columns associated to get the result) for the final pivot you are trying? It is relatively easy once you get the hang of it. — DRapp, Dec 30 '13 at 20:23
Although this can be done using pivot (see this fiddle http://sqlfiddle.com/#!6/d9ed2/5/0), I recommend that you solve the problem in the front-end (HTML or XAML) where can deal with the data easily. — Eduardo Molteni, Dec 30 '13 at 21:10
Are the test values known ahead of time or are you going to have an unlimited number of tests? — Taryn, Dec 30 '13 at 22:42

score 2 · Answer 1 · answered Dec 30 '13 at 20:57

Test Data

CREATE TABLE #myTable (PtName NVARCHAR(20),Age INT,Race NVARCHAR(30),Ethnicity NVARCHAR(50)
                        ,DrawDate DATE, TestType NVARCHAR(50), TestResult NUMERIC(20,1))
GO
INSERT INTO #myTable
VALUES
('PT001',17,'White','Non-Hispanic','05/17/2011','WBC',6.8),
('PT001',17,'White','Non-Hispanic','05/17/2011','HGB',14.1),
('PT001',17,'White','Non-Hispanic','05/17/2011','Platelets',142.0),
('PT001',17,'White','Non-Hispanic','05/29/2011','WBC',7.2),
('PT002',34,'White','Hispanic','05/17/2011','WBC',7.8),
('PT002',34,'White','Hispanic','05/17/2011','Platelets',255.0)

Query

SELECT PtName, Age, Race, Ethnicity, DrawDate, [WBC], [HGB], [Platelets]
FROM (SELECT * FROM #myTable)t
  PIVOT (
         MAX(TestResult)
         FOR TestType
         IN ([WBC], [HGB], [Platelets])
         )p

Result Set

╔════════╦═════╦═══════╦══════════════╦════════════╦═════╦══════╦═══════════╗
║ PtName ║ Age ║ Race  ║  Ethnicity   ║  DrawDate  ║ WBC ║ HGB  ║ Platelets ║
╠════════╬═════╬═══════╬══════════════╬════════════╬═════╬══════╬═══════════╣
║ PT001  ║  17 ║ White ║ Non-Hispanic ║ 2011-05-17 ║ 6.8 ║ 14.1 ║ 142.0     ║
║ PT001  ║  17 ║ White ║ Non-Hispanic ║ 2011-05-29 ║ 7.2 ║ NULL ║ NULL      ║
║ PT002  ║  34 ║ White ║ Hispanic     ║ 2011-05-17 ║ 7.8 ║ NULL ║ 255.0     ║
╚════════╩═════╩═══════╩══════════════╩════════════╩═════╩══════╩═══════════╝

I was able to get this working using a variation of your suggestion. I had tried something similar before but must have been missing the magic piece. Thank you so much for your help! — user3147235, Jan 04 '14 at 13:47

score 0 · Answer 2 · answered Dec 30 '13 at 20:35

I have made something like this:

SELECT 
x.PtName,
x.Age,
x.Race,
x.Ethnicity,
x.DrawDate, 
(
    SELECT y.TestResult
    FROM `TABLE 1` as `y` 
    WHERE x.TestType = 'WBC' 
    AND x.PtName = y.PtName
    AND x.DrawDate = y.DrawDate
    LIMIT 1
) AS 'WBC',
(
    SELECT z.TestResult
    FROM `TABLE 1` as `z` 
    WHERE z.TestType = 'HGB' 
    AND x.PtName = z.PtName
    AND x.DrawDate = z.DrawDate
    LIMIT 1
) AS 'HGB',
(
    SELECT v.TestResult
    FROM `TABLE 1` as `v` 
    WHERE v.TestType = 'Platelets' 
    AND x.PtName = v.PtName
    AND x.DrawDate = v.DrawDate
    LIMIT 1
) AS 'Platelets'
FROM `TABLE 1` as `x`
GROUP BY PtName, DrawDate

I'm sure this is not very fast, but it works at least for me ;)

I was able to get this working using a variation of the first suggestion above. I had tried something similar before but must have been missing the magic piece. Thank you so much for trying to help me though! — user3147235, Jan 04 '14 at 13:47

SQL PIVOT(?) query

2 Answers2