如何在中创建枢轴表？

Question

关于堆栈溢出的第一个问题。我在一家医疗检测公司工作，他们需要一张表格，显示2021年每个州有多少人接受了每种类型的检测。我的adr.state表中有所有的状态，p.name表中有所有的测试类型。

我编写了以下查询：

SELECT
    adr.state,
    'TaqPath COVID-19 rRT-PCR Assay',
    'TaqPath COVID-19 rRT-PCR Assay (Saliva Specimen)',
    'TaqPath COVID-19 rRT-PCR Assay (Throat Specimen)',
    'Beckman Access SARS-CoV-2 IgG Test',
    'PSA, Free',
    'Colorectal Cancer Current Risk'
FROM
    (SELECT DISTINCT 
         r.requisitionNumber,
         adr.state,
         p.name
     FROM
         copia.copia.Requisition AS r
     JOIN
         copia.copia.OrderedPanel AS op ON op.requisitionKey = r.requisitionKey
     JOIN
         copia.copia.panel AS p ON p.panelKey = op.panelKey
     JOIN
         copia.copia.Location AS loc ON loc.locationKey = r.orderingLocationKey
     JOIN
         copia.copia.Address AS adr ON adr.addressKey = loc.addressKey
     WHERE
         copia.dbo.FixDate(r.finalDeliveryStamp) BETWEEN '1/1/2021' AND '4/24/2021'
         AND p.name NOT LIKE '%body mass index%'
         AND p.name NOT LIKE '%hold account%') p 
    PIVOT 
        (SUM(adr.state) 
            FOR p.name IN (['TaqPath COVID-19 rRT-PCR Assay'],
                           ['TaqPath COVID-19 rRT-PCR Assay (Saliva Specimen)'],
                           ['TaqPath COVID-19 rRT-PCR Assay (Throat Specimen)'],
                           ['Beckman Access SARS-CoV-2 IgG Test'],
                           ['PSA, Free'],
                           ['Colorectal Cancer Current Risk'])
        ) AS PVT;

我做错了什么？我知道这些错误：

Msg 107，15级，状态1，第34行

列前缀'adr‘与查询中使用的表名或别名不匹配。

Msg 4104，第16级，状态1，第2行

多部分标识符"adr.state“无法绑定。

在回顾中，我需要的是一个矩阵，其中列是测试名称，行是每种状态类型，其中的值是与该状态匹配的测试类型的总和。

非常感谢!

Answer 1

在您的外部查询中，将无法识别adr。相反，只使用state：

SELECT
     state,
    'TaqPath COVID-19 rRT-PCR Assay',
    'TaqPath COVID-19 rRT-PCR Assay (Saliva Specimen)',
    'TaqPath COVID-19 rRT-PCR Assay (Throat Specimen)',
    'Beckman Access SARS-CoV-2 IgG Test',
    'PSA, Free',
    'Colorectal Cancer Current Risk'
FROM
    (SELECT DISTINCT 
         r.requisitionNumber,
         adr.state,
         p.name
     FROM
         copia.copia.Requisition AS r
     JOIN
         copia.copia.OrderedPanel AS op ON op.requisitionKey = r.requisitionKey
     JOIN
         copia.copia.panel AS p ON p.panelKey = op.panelKey
     JOIN
         copia.copia.Location AS loc ON loc.locationKey = r.orderingLocationKey
     JOIN
         copia.copia.Address AS adr ON adr.addressKey = loc.addressKey
     WHERE
         copia.dbo.FixDate(r.finalDeliveryStamp) BETWEEN '1/1/2021' AND '4/24/2021'
         AND p.name NOT LIKE '%body mass index%'
         AND p.name NOT LIKE '%hold account%') p 
    PIVOT 
        (SUM(adr.state) 
            FOR p.name IN (['TaqPath COVID-19 rRT-PCR Assay'],
                           ['TaqPath COVID-19 rRT-PCR Assay (Saliva Specimen)'],
                           ['TaqPath COVID-19 rRT-PCR Assay (Throat Specimen)'],
                           ['Beckman Access SARS-CoV-2 IgG Test'],
                           ['PSA, Free'],
                           ['Colorectal Cancer Current Risk'])
        ) AS PVT;