在透视此数据时遇到问题

Question

MS Server 2012

下面的查询返回了正确的数据，但我需要对其进行透视，并且我的尝试失败了，任何帮助都将不胜感激。

查询...

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列中以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

Answer 1

这解决了它，Thandk 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