sql - 无法旋转此数据
问题描述
微软服务器 2012
以下查询返回正确的数据,但我需要对其进行旋转,但我的尝试失败了,任何 hep 将不胜感激..
询问...
SELECT
TrialSampleID,
ReferenceReadID,
Reference_Antibiotic,
CONCAT(Reference_Qualifier, ' ', Reference_MIC, ' ug/ml') AS 'MIC'
FROM
dbo.ClinicalTrial
JOIN dbo.ReferenceDataPointers ON ClinicalTrial.ID = ReferenceDataPointers.ClinicalTrialID
JOIN dbo.ReferencePlates ON ReferenceDataPointers.ReferenceSetID = ReferencePlates.ReferenceSetID
JOIN dbo.ReferenceReads ON ReferencePlates.ID = ReferenceReads.ReferencePlateID
JOIN dbo.ReferenceMICs ON ReferenceReads.ID = ReferenceMICs.ReferenceReadID
WHERE
TrialSampleID NOT LIKE 'REF-%'
AND SpecimenSource LIKE 'Clinical%'
AND Reference_ValidAntibiotic = 'True
'
结果...
TrialSampleID |ReferenceReadID |Reference_Antibiotic |MIC
CC-200001 | 4077 |Amikacin | = 8 ug/ml
CC-200001 | 4077 |Ampicillin | > 64 ug/ml
CC-200001 | 4077 |Aztreonam | > 64 ug/ml
CC-200001 | 4077 |Cefazolin | > 16 ug/ml
CC-200001 | 4077 |Cefepime | = 64 ug/ml
CC-200001 | 4077 |Ceftazidime | > 64 ug/ml
CC-200001 | 4077 |Ceftazidime/Avibactam | ≤ 2 ug/ml
CC-200001 | 4077 |Ertapenem | ≤ 0.125 ug/ml
CC-200001 | 4077 |Gentamicin | > 32 ug/ml
CC-200001 | 4077 |Levofloxacin | = 8 ug/ml
CC-200001 | 4077 |Meropenem | ≤ 0.125 ug/ml
CC-200001 | 4077 |Meropenem/Vaborbactam | ≤ 0.5 ug/ml
CC-200001 | 4077 |Piperacillin/Tazobactam | = 128 ug/ml
CC-200001 | 4077 |Trimethoprim/Sulfamethoxazole | > 8 ug/ml
该块对每个 TrialSampleID 重复 6 次,给出 6 个 MIC
我想要以下形式的数据,但当我旋转数据时,MIC 列中的数据为 NULL。
想要...
TrialSampleID |ReferenceReadID |Reference_Antibiotic |MIC1 |MIC2 |MIC3
CC-200001 | 4077 |Amikacin | = 8 ug/ml | = 8 ug/ml | = 8 ug/ml
CC-200001 | 4077 |Ampicillin | > 64 ug/ml | > 64 ug/ml | > 64 ug/ml
CC-200001 | 4077 |Aztreonam | > 64 ug/ml | > 64 ug/ml | > 64 ug/ml
CC-200001 | 4077 |Cefazolin | > 16 ug/ml | > 16 ug/ml | > 16 ug/ml
CC-200001 | 4077 |Cefepime | = 64 ug/ml | = 64 ug/ml | = 64 ug/ml
CC-200001 | 4077 |Ceftazidime | > 64 ug/ml | > 64 ug/ml | > 64 ug/ml
CC-200001 | 4077 |Ceftazidime/Avibactam | ≤ 2 ug/ml | ≤ 2 ug/ml | ≤ 2 ug/ml
CC-200001 | 4077 |Ertapenem | ≤ 0.125 ug/ml | ≤ 0.125 ug/ml | ≤ 0.125 ug/ml
CC-200001 | 4077 |Gentamicin | > 32 ug/ml | > 32 ug/ml | > 32 ug/ml
CC-200001 | 4077 |Levofloxacin | = 8 ug/ml | = 8 ug/ml | = 8 ug/ml
CC-200001 | 4077 |Meropenem | ≤ 0.125 ug/ml | ≤ 0.125 ug/ml | ≤ 0.125 ug/ml
CC-200001 | 4077 |Meropenem/Vaborbactam | ≤ 0.5 ug/ml | ≤ 0.5 ug/ml | ≤ 0.5 ug/ml
CC-200001 | 4077 |Piperacillin/Tazobactam | = 128 ug/ml | = 128 ug/ml | = 128 ug/ml
CC-200001 | 4077 |Trimethoprim/Sulfamethoxazole | > 8 ug/ml | > 8 ug/ml | > 8 ug/ml
试过...
PIVOT(
MIN(ReferenceReadID)
FOR MIC IN (
MIC1,
MIC2,
MIC3,
MIC4,
MIC5,
MIC6
)
) pivoted
解决方案
这解决了它,感谢 D-Shih 的洞察力......
SELECT TrialSampleID,
Reference_Antibiotic,
MAX(CASE WHEN rn = 1 THEN MIC END) 'MIC1',
MAX(CASE WHEN rn = 2 THEN MIC END) 'MIC2',
MAX(CASE WHEN rn = 3 THEN MIC END) 'MIC3',
MAX(CASE WHEN rn = 4 THEN MIC END) 'MIC4',
MAX(CASE WHEN rn = 5 THEN MIC END) 'MIC5',
MAX(CASE WHEN rn = 6 THEN MIC END) 'MIC6'
FROM (
SELECT
TrialSampleID,
Reference_Antibiotic,
CONCAT(Reference_Qualifier, ' ', Reference_MIC, ' ug/ml') AS 'MIC',
ROW_NUMBER() OVER(PARTITION BY TrialSampleID, Reference_Antibiotic ORDER BY TrialSampleID, Reference_Antibiotic) rn
FROM
dbo.ClinicalTrial
JOIN dbo.ReferenceDataPointers ON ClinicalTrial.ID = ReferenceDataPointers.ClinicalTrialID
JOIN dbo.ReferencePlates ON ReferenceDataPointers.ReferenceSetID = ReferencePlates.ReferenceSetID
JOIN dbo.ReferenceReads ON ReferencePlates.ID = ReferenceReads.ReferencePlateID
JOIN dbo.ReferenceMICs ON ReferenceReads.ID = ReferenceMICs.ReferenceReadID
WHERE
TrialSampleID NOT LIKE 'REF-%'
AND SpecimenSource LIKE 'Clinical%'
AND Reference_ValidAntibiotic = 'True'
) t1
GROUP BY TrialSampleID, Reference_Antibiotic
ORDER BY TrialSampleID, Reference_Antibiotic
推荐阅读
- mysql - 用子查询排序奇怪
- ruta - UIMA RUTA 在运行时扩展单词表
- ios - 如何以编程方式从意图中获取模板化响应字符串?
- ansible - Ansible Jinja2 从字典内的列表中获取元素
- google-app-maker - 查询引用其他数据源的数据源
- javascript - 单击模态内容内部时模态关闭
- java - 从模板字符串中查找值
- php - 根据内部数组的值和重复值重新排列多维数组
- php - 加入查询以获得不同的结果
- entity-framework - DbContext SaveChanges - System.Data.SqlClient.SqlException:执行超时已过期