SQL PIVOT(?) query

Question

我花了一整天的时间在这上面，但就是不明白。通常我对这些东西很在行，但我只是回到SQL，就是弄不明白。如果您能提供任何帮助，我们将不胜感激。

我正在运行SQL Server 2008 R2。我有一个数据库，保存患者数据(没有姓名，只有标识符)和临床测试结果。我已经建立了一个视图来查询各种数据表，并将我需要的信息收集到一个位置。我的查询结果给了我一个这样的表：(测试和值是虚构的——我对测试一无所知。我只是技术人员——哈哈。)

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

我想要的是：

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

我制定了一个PIVOT查询(第一个-非常自豪，不起作用-不那么自豪)，但后来意识到在PIVOT中使用聚合不会为每个患者提供多次就诊。

我正在与数百名患者打交道，有些患者多次就诊，每次最多进行八次检测。PIVOT查询是处理此问题的最佳方式吗？有更好的方法吗？也许是某种形式的子查询？我愿意尝试任何事情。

Answer 1

测试数据

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)

查询

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

结果集

╔════════╦═════╦═══════╦══════════════╦════════════╦═════╦══════╦═══════════╗
║ 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     ║
╚════════╩═════╩═══════╩══════════════╩════════════╩═════╩══════╩═══════════╝

Answer 2

我做了这样的东西：

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

我确信这不是很快，但至少对我有效；)