sql - 将多行/列旋转为 1 行

首先要了解枢轴，您在结果集中选择一个列作为PIVOT锚点，即数据将围绕枢轴旋转的铰链，这在FOR子句中指定。

您只能PIVOT FOR使用单个列，但您可以在子查询中或从联接或视图构建此列作为目标数据查询，OP已使用ROW_NUMBER()但您可以使用任何您希望的 SQL 机制，甚至CASE语句来构建自定义列以围绕如果数据集中没有要使用的自然列。

PIVOT将为列中的每个值创建一个列，并为该FOR列提供您指定的聚合函数的值

它有助于可视化构建的记录集，在应用数据透视之前，以下 SQL 可以重新创建 OP 呈现的数据场景。我在这里使用了表变量来代替 OP 表和视图。

-- template13_ticker_data (with sector_char added)
DECLARE @tickerData Table
(
    sector INT,
    ticker CHAR(4),
    m_ticker CHAR(4),
    sector_char char(10)
)
-- template13_vw
DECLARE @Company Table
(
    m_ticker CHAR(4),
    ticker CHAR(4),
    company_name VARCHAR(100)
)

INSERT INTO @tickerData (sector, ticker)
VALUES (5 ,'ADNT')
,(5 ,'AUTO')
,(5 ,'THRM')
,(5 ,'ALSN')
,(5 ,'ALV')
,(12,'HES')
,(12,'AM')
,(12,'PHX')
,(12,'NBR')
,(12,'AMRC')


INSERT INTO @Company (ticker, company_name)
VALUES ('ADNT','Adient PLC')
,('AUTO','Autobytel Inc.')
,('THRM','Gentherm Inc')
,('ALSN','Allison Transmission Holdings, Inc.')
,('ALV ','Autoliv, Inc.')
,('HES ','Hess Corporation')
,('AM  ','Antero Midstrm')
,('PHX ','Panhandle Royalty Company')
,('NBR ','Nabors Industries Ltd.')
,('AMRC','Ameresco, Inc.')

-- Just re-creating a record set that matches the given data and query structure
UPDATE @tickerData SET m_ticker = ticker
UPDATE @Company SET m_ticker = ticker
-- populate 'sector_char' to show multiple aggregates
UPDATE @tickerData SET sector_char = '|' + cast(sector as varchar) + '|'

-- Unpivoted data Proof
SELECT d.sector, d.sector_char, d.ticker, v.company_name, ROW_NUMBER() OVER(PARTITION BY d.sector ORDER BY d.sector) rn
FROM @tickerData d, @Company v
WHERE d.m_ticker = v.m_ticker

枢轴之前的数据如下所示：

sector  sector_char ticker  company_name                           rn
------------------------------------------------------------------------
5       |5|         ADNT     Adient PLC                            1
5       |5|         AUTO     Autobytel Inc.                        2
5       |5|         THRM     Gentherm Inc                          3
5       |5|         ALSN     Allison Transmission Holdings, Inc.   4
5       |5|         ALV      Autoliv, Inc.                         5
12      |12|        HES      Hess Corporation                      1
12      |12|        AM       Antero Midstrm                        2
12      |12|        PHX      Panhandle Royalty Company             3
12      |12|        NBR      Nabors Industries Ltd.                4
12      |12|        AMRC     Ameresco, Inc.                        5

现在可视化您期望的结果子集，以显示我创建sector_char的要包含在最终输出中的多列操作的限制

sector  sector_char ticker_1    company_1          ticker_2   company_2
-----------------------------------------------------------------------------
5       |5|         ADNT        Adient PLC         AUTO       Autobytel Inc.
12      |12|        HES         Hess Corporation   AM         Antero Midstrm

ticker因为我们希望原始行输出（以及每一行）的多于 1 列输出，所以company我们必须使用以下技术之一：

将多列中的值连接成一列
- 仅当您可以在需要使用单个值之前轻松拆分这些列时才有用，或者如果您不需要处理列，它纯粹是为了可视化。
执行多个PIVOT查询并加入结果
- 当每列的聚合逻辑不同时，或者您不是简单地将行值转换为列值（将多行聚合为单个单元格响应）时，这是必需的。
- 在这样的场景中，当我们只是转置值时（例如，聚合的结果将与原始单元格值匹配），我认为这有点hack，但也可以比替代方案更少语法。
  
  我说hack是因为核心 PIVOT 逻辑是重复的，这使得随着查询的发展更难维护。
在唯一列上执行单个PIVOT，在其他表上连接以构建附加列
- 这很容易在输出中允许无限数量的附加行。PIVOT解析包含我们希望在最终结果中显示的多个值的表的 ID 。

让我们先看 3，因为这演示了单个 PIVOT 以及如何为每个 PIVOT 结果包含多个列：

在此示例中，我允许每个扇区最多有 8 个结果，请务必注意，您必须指定中的所有输出列PIVOT，它不是动态的。

您可以使用动态查询来测试您需要的最大列数，并根据这些结果生成以下查询。

另请注意，在此解决方案中，我们不需要加入源查询中的template13_vw表PIVOT，而是加入了结果，这就是为什么枢轴返回m_ticker（我假设它是键）而不是ticker显示的原因在最终结果中。

-- NOTE: using CTE here, you could use table variables, temporary tables or whatever else you need
;WITH TickersBySector as
(
    -- You must specify the fixed number of columns in the output
    SELECT sector, sector_char, [1] as [m_ticker_1],[2] as [m_ticker_2],[3] as [m_ticker_3],[4] as [m_ticker_4],[5] as [m_ticker_5],[6] as [m_ticker_6],[7] as [m_ticker_7],[8] as [m_ticker_8]
    FROM (
        SELECT d.sector, d.sector_char, d.m_ticker, ROW_NUMBER() OVER(PARTITION BY d.sector ORDER BY d.sector) rn
        FROM template13_ticker_data d /* OPs Syntax */
        -- FROM @tickerData d /* Use this with the proof table variables */
        ) data
    PIVOT (
      MAX(m_ticker)
      FOR rn IN ( [1],[2],[3],[4],[5],[6],[7],[8])
    ) as PivotTable
)
-- To use with the proof table variables, replace 'template13_vw' with '@Company'
SELECT sector, sector_char
    ,c1.[ticker] as [ticker_1], c1.company_name as [company_1] 
    ,c2.[ticker] as [ticker_2], c2.company_name as [company_2] 
    ,c3.[ticker] as [ticker_3], c3.company_name as [company_3] 
    ,c4.[ticker] as [ticker_4], c4.company_name as [company_4] 
    ,c5.[ticker] as [ticker_5], c5.company_name as [company_5] 
    ,c6.[ticker] as [ticker_6], c6.company_name as [company_6] 
    ,c7.[ticker] as [ticker_7], c7.company_name as [company_7] 
    ,c8.[ticker] as [ticker_8], c8.company_name as [company_8]
FROM TickersBySector
LEFT OUTER JOIN template13_vw c1 ON c1.m_ticker = TickersBySector.m_ticker_1
LEFT OUTER JOIN template13_vw c2 ON c2.m_ticker = TickersBySector.m_ticker_2
LEFT OUTER JOIN template13_vw c3 ON c3.m_ticker = TickersBySector.m_ticker_3
LEFT OUTER JOIN template13_vw c4 ON c4.m_ticker = TickersBySector.m_ticker_4
LEFT OUTER JOIN template13_vw c5 ON c5.m_ticker = TickersBySector.m_ticker_5
LEFT OUTER JOIN template13_vw c6 ON c6.m_ticker = TickersBySector.m_ticker_6
LEFT OUTER JOIN template13_vw c7 ON c7.m_ticker = TickersBySector.m_ticker_7
LEFT OUTER JOIN template13_vw c8 ON c8.m_ticker = TickersBySector.m_ticker_8

以下是同一个查询，使用多个PIVOT查询连接在一起。PIVOT请注意，在这种情况下，两者都带回额外的公共列并不重要sector_char，因此当聚合或额外的公共列对于不同的结果集可能不同时，请使用这种语法风格。

;WITH TickersBySector as
(
    -- You must specify the fixed number of columns in the output
    SELECT sector, sector_char, [1] as [ticker_1],[2] as [ticker_2],[3] as [ticker_3],[4] as [ticker_4],[5] as [ticker_5],[6] as [ticker_6],[7] as [ticker_7],[8] as [ticker_8]
    FROM (
        SELECT d.sector, d.sector_char, d.m_ticker, ROW_NUMBER() OVER(PARTITION BY d.sector ORDER BY d.sector) rn
        FROM template13_ticker_data d /* OPs Syntax */
        -- FROM @tickerData d /* Use this with the proof table variables */
        ) data
    PIVOT (
      MAX(m_ticker)
      FOR rn IN ( [1],[2],[3],[4],[5],[6],[7],[8])
    ) as PivotTable
)
, CompanyBySector as
(
    -- You must specify the fixed number of columns in the output
    SELECT sector,[1] as [company_1],[2] as [company_2],[3] as [company_3],[4] as [company_4],[5] as [company_5],[6] as [company_6],[7] as [company_7],[8] as [company_8]
    FROM (
        SELECT d.sector, v.company_name, ROW_NUMBER() OVER(PARTITION BY d.sector ORDER BY d.sector) rn
        FROM template13_ticker_data d /* OPs Syntax */
        -- FROM @tickerData d /* Use this with the proof table variables */
        INNER JOIN template13_vw v /* OPs Syntax */
        -- INNER JOIN @Company v /* Use this with the proof table variables */
            ON d.m_ticker = v.m_ticker
        ) data
    PIVOT (
      MAX(company_name)
      FOR rn IN ( [1],[2],[3],[4],[5],[6],[7],[8])
    ) as PivotTable
)
SELECT TickersBySector.sector, sector_char
    ,[ticker_1], [company_1] 
    ,[ticker_2], [company_2] 
    ,[ticker_3], [company_3] 
    ,[ticker_4], [company_4] 
    ,[ticker_5], [company_5] 
    ,[ticker_6], [company_6] 
    ,[ticker_7], [company_7] 
    ,[ticker_8], [company_8]
FROM TickersBySector
INNER JOIN CompanyBySector ON TickersBySector.sector = CompanyBySector.sector

sql - 将多行/列旋转为 1 行

问题描述

解决方案

推荐阅读