如何以嵌套方式透视2个或多个值

Question

我目前正在为一个客户端开发导出视图，他们请求创建一个带有附加列和第一个透视列的透视图，以便添加额外的细节。

我有这个：

SAMPLE_TAG   SAMPLE_TYPE    LAB_METHOD  LAB_ELEMENT LAB_RESULT_NUMERIC
MCR0004140   OR             ME-MS41      Ag          0.03
MCR0004140   OR             ME-MS41      Au         -0.2
MCR0004140   OR             ME-MS41      Cu         15.3
MCR0004140   OR             ME-MS41      Zn         19
MCR0005015   OR             AG-OG46      Ag         421
MCR0005015   OR             ME-MS41      Au         -0.2
MCR0005015   OR             ME-MS41      Cu         30.7
MCR0005015   OR             ME-MS41      Zn         15

我想实现这个：

SAMPLE_TAG  SAMPLE_TYPE     Ag      Ag_Method   Au      Au_Method   Cu      Cu_Method   Zn  Zn_Method
MCR0004140  OR              0.03    ME-MS41     -0.2    ME-MS41 15.3        ME-MS41     19  ME-MS41
MCR0005015  OR              421     AG-OG46     -0.2    ME-MS41 30.7        ME-MS41     15  ME-MS41

我可以使用以下方法获得第一个枢轴：

SELECT *
FROM TEMP_PIVOT
PIVOT (
      MAX(LAB_RESULT_NUMERIC)
      FOR LAB_ELEMENT  IN ( [Au],[Ag],[Cu],[Zn],[Pt]) ) AS RESULT

然而，这就是我陷入困境的地方。

如有任何帮助或建议，我们将不胜感激。我在下面附上了创建表/插入脚本：

谢谢：）

---

CREATE TABLE [dbo].[TEMP_PIVOT](
[LAB_ID] [nvarchar](12) NOT NULL,
[DESPATCH_ID] [nvarchar](30) NULL,
[LAB_JOB_NO] [nvarchar](20) NULL,
[PROJECT] [nvarchar](16) NULL,
[SITE_ID] [nvarchar](16) NULL,
[SAMPLE_TAG] [nvarchar](16) NULL,
[SAMPLE_TYPE] [nvarchar](8) NULL,
[LAB_METHOD] [nvarchar](30) NOT NULL,
[LAB_ELEMENT] [nvarchar](8) NULL,
[LAB_RESULT_NUMERIC] [float] NULL) ON [PRIMARY]

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Ag', 0.03)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Au', -0.2)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Cu', 15.3)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Zn', 19)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'AG-OG46', N'Ag', 421)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Au', -0.2)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Cu', 30.7)
INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Zn', 15)

Answer 1

如果您希望查询动态运行，请尝试此

Fiddle演示在这里

DECLARE @L_ELEMENT       VARCHAR(max)='', 
        @aggL_ELEMENTMTD VARCHAR(max)='', 
        @L_ELEMENTMTD    VARCHAR(max)='', 
        @aggL_ELEMENT    VARCHAR(max)='', 
        @sql             NVARCHAR(max) 
SET @L_ELEMENT = (SELECT DISTINCT Quotename(lab_element) + ',' 
              FROM   temp_pivot 
              FOR xml path('')) 
SET @aggL_ELEMENTMTD = (SELECT DISTINCT ' max(' 
                                    + Quotename(lab_element+'_Method') + 
                                    ') ' 
                                    + Quotename(lab_element+'_Method') + ',' 
                    FROM   temp_pivot 
                    FOR xml path('')) 
SET @L_ELEMENTMTD = (SELECT DISTINCT Quotename(lab_element+'_Method') + ',' 
                 FROM   temp_pivot 
                 FOR xml path('')) 
SET @aggL_ELEMENT = (SELECT DISTINCT ' max(' + Quotename(lab_element) + ') ' 
                                 + Quotename(lab_element) + ',' 
                 FROM   temp_pivot 
                 FOR xml path('')) 
SELECT @L_ELEMENTMTD = LEFT(@L_ELEMENTMTD, Len(@L_ELEMENTMTD) - 1) 
SELECT @L_ELEMENT = LEFT(@L_ELEMENT, Len(@L_ELEMENT) - 1) 
SELECT @aggL_ELEMENT = LEFT(@aggL_ELEMENT, Len(@aggL_ELEMENT) - 1) 
SELECT @aggL_ELEMENTMTD = LEFT(@aggL_ELEMENTMTD, Len(@aggL_ELEMENTMTD) - 1) 
SET @sql = 'SELECT SAMPLE_TAG,SAMPLE_TYPE,' + @aggL_ELEMENT + ',' 
       + @aggL_ELEMENTMTD + ' FROM (select LAB_ELEMENT+''_Method'' L_ELEMENTMTD,* 
                  FROM TEMP_PIVOT) AS T 
 PIVOT (max(LAB_RESULT_NUMERIC) FOR LAB_ELEMENT
 IN      (' + @L_ELEMENT + ')) AS P1    
 PIVOT       (max(LAB_METHOD) FOR L_ELEMENTMTD 
 IN      (' + @L_ELEMENTMTD + ')) AS P1        
          group by SAMPLE_TAG, SAMPLE_TYPE' 
--PRINT @sql 
EXEC Sp_executesql 
  @sql 

Answer 2

仅供参考（如果您不关心解决方案是否与pivot有关，而是为您获得业务问题的结果），这里有一个快速且更简单的替代方案。

SELECT
    SAMPLE_TAG,
    SAMPLE_TYPE,
    MAX(CASE WHEN LAB_ELEMENT = 'Ag' THEN LAB_RESULT_NUMERIC ELSE NULL END) AS [Ag],
    MAX(CASE WHEN LAB_ELEMENT = 'Ag' THEN LAB_METHOD ELSE NULL END) AS [Ag_Method],
    MAX(CASE WHEN LAB_ELEMENT = 'Au' THEN LAB_RESULT_NUMERIC ELSE NULL END) AS [Au],
    MAX(CASE WHEN LAB_ELEMENT = 'Au' THEN LAB_METHOD ELSE NULL END) AS [Au_Method],
    MAX(CASE WHEN LAB_ELEMENT = 'Cu' THEN LAB_RESULT_NUMERIC ELSE NULL END) AS [Cu],
    MAX(CASE WHEN LAB_ELEMENT = 'Cu' THEN LAB_METHOD ELSE NULL END) AS [Cu_Method],
    MAX(CASE WHEN LAB_ELEMENT = 'Zn' THEN LAB_RESULT_NUMERIC ELSE NULL END) AS [Zn],
    MAX(CASE WHEN LAB_ELEMENT = 'Zn' THEN LAB_METHOD ELSE NULL END) AS [Zn_Method],
    MAX(CASE WHEN LAB_ELEMENT = 'Pt' THEN LAB_RESULT_NUMERIC ELSE NULL END) AS [Pt],
    MAX(CASE WHEN LAB_ELEMENT = 'Pt' THEN LAB_METHOD ELSE NULL END) AS [Pt_Method]
FROM dbo.TEMP_PIVOT
GROUP BY
    SAMPLE_TAG,
    SAMPLE_TYPE  

输出

SAMPLE_TAG       SAMPLE_TYPE Ag                     Ag_Method                      Au                     Au_Method                      Cu                     Cu_Method                      Zn                     Zn_Method                      Pt                     Pt_Method
---------------- ----------- ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------
MCR0004140       OR          0.03                   ME-MS41                        -0.2                   ME-MS41                        15.3                   ME-MS41                        19                     ME-MS41                        NULL                   NULL
MCR0005015       OR          421                    AG-OG46                        -0.2                   ME-MS41                        30.7                   ME-MS41                        15                     ME-MS41                        NULL                   NULL

Answer 3

这是带有多枢轴的版本

;WITH E AS (
    SELECT
        SAMPLE_TAG,
        SAMPLE_TYPE,
        LAB_ELEMENT AS E1,
        LAB_ELEMENT + '_Method' AS E2,
        LAB_RESULT_NUMERIC,
        LAB_METHOD
    FROM dbo.TEMP_PIVOT
)
    SELECT
        SAMPLE_TAG,
        SAMPLE_TYPE,
        MAX([Ag]) AS [Ag],
        MAX([Ag_Method]) AS [Ag_Method],
        MAX([Au]) AS [Au],
        MAX([Au_Method]) AS [Au_Method],
        MAX([Cu]) AS [Cu],
        MAX([Cu_Method]) AS [Cu_Method],
        MAX([Zn]) AS [Zn],
        MAX([Zn_Method]) AS [Zn_Method],
        MAX([Pt]) AS [Pt],
        MAX([Pt_Method]) AS [Pt_Method]
    FROM E
        PIVOT ( MAX(LAB_RESULT_NUMERIC) FOR E1 IN ( [Au],[Ag],[Cu],[Zn],[Pt]) ) AS R1
        PIVOT ( MAX(LAB_METHOD) FOR E2 IN ( [Au_Method],[Ag_Method],[Cu_Method],[Zn_Method],[Pt_Method]) ) AS R2
    GROUP BY
        SAMPLE_TAG,
        SAMPLE_TYPE
    ORDER BY
        SAMPLE_TAG,
        S

输出是相同的

SAMPLE_TAG       SAMPLE_TYPE Ag                     Ag_Method                      Au                     Au_Method                      Cu                     Cu_Method                      Zn                     Zn_Method                      Pt                     Pt_Method
---------------- ----------- ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------ ---------------------- ------------------------------
MCR0004140       OR          0.03                   ME-MS41                        -0.2                   ME-MS41                        15.3                   ME-MS41                        19                     ME-MS41                        NULL                   NULL
MCR0005015       OR          421                    AG-OG46                        -0.2                   ME-MS41                        30.7                   ME-MS41                        15                     ME-MS41                        NULL                   NULL