前端之家

  1. 首页
  2. 问答

枢转此数据时遇到问题

• 问答

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

我希望采用以下格式的数据，但是在我旋转数据时，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
heminminzjl 回答：枢转此数据时遇到问题

这可以解决，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,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
pivotsqlsql-servertsql
本文链接：https://www.f2er.com/3165979.html

大家都在问