首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将多行/列旋转为1行

问将多行/列旋转为1行
EN

Stack Overflow用户
提问于 2019-06-19 22:29:38
回答 1查看 3.3K关注 0票数 2

我们需要使用多行和多列,并将它们转换为每键1行。我有一个枢轴查询,但它不起作用。我得到了一些关于“列定义不明确”的错误

我们的数据如下:

代码语言:javascript
复制
SECTOR   TICKER  COMPANY
-----------------------------------------------------
5         ADNT    Adient PLC
5         AUTO    Autobytel Inc.
5         THRM    Gentherm Inc
5         ALSN    Allison Transmission Holdings, Inc.
5         ALV     Autoliv, Inc.
12        HES     Hess Corporation
12        AM      Antero Midstrm
12        PHX     Panhandle Royalty Company
12        NBR     Nabors Industries Ltd.
12        AMRC     Ameresco, Inc.

我们需要的是每个ID 1行,每个代码/公司都在一个不同的列中。因此,输出看起来应该是:

代码语言:javascript
复制
5 ADNT   Adient PLC   AUTO  Autobytel Inc.   THRM  Gentherm Inc........

你明白了吧。每个ID 1行,以及它自己的列中的彼此值。我尝试的查询是:

代码语言:javascript
复制
SELECT sector, ticker, company_name
FROM (SELECT d.sector, d.ticker, v.company_name, ROW_NUMBER() OVER(PARTITION BY d.sector ORDER BY d.sector) rn
           FROM template13_ticker_data d, template13_vw v
           WHERE d.m_ticker = v.m_ticker) 
PIVOT (MAX(sector) AS sector, MAX(ticker) AS ticker, MAX(company_name) AS company_name
 FOR (rn) IN (1 AS sector, 2 AS ticker, 3 AS company_name))
ORDER BY sector;
EN

回答 1

Stack Overflow用户

发布于 2019-06-20 01:47:19

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

您只能使用PIVOT FOR一列,但您可以在子查询中或从联接或视图中构造该列作为目标数据查询,OP使用了ROW_NUMBER(),但您可以使用任何您希望使用的SQL机制,甚至可以使用CASE语句来构建自定义列,以便在数据集中没有自然列可使用时进行转换。

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

它有助于可视化构造的记录集,在应用支点之前,下面的SQL可以重新创建OP提供的数据场景。这里我使用了表变量来代替OPs表和视图。

代码语言:javascript
复制
-- 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

支点之前的数据如下所示:

代码语言:javascript
复制
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在最终输出中包含的多列操作的局限性。

代码语言:javascript
复制
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和company ),所以我们必须使用以下技术之一:

  1. 将来自多个列的值串联到单个列中
    • 只有当您可以在使用单个值之前轻松地拆分这些列时,或者如果您不需要处理这些列,那么它是完全用于可视化的。

​

  1. 执行多个PIVOT查询并加入结果
    • 当每个列的聚合逻辑不同时,或者不是简单地将行值转换为列值(将多个行聚合为单个单元格响应)时,就必须这样做。
    • 在这种情况下,当我们只是转换值时(例如,聚合的结果将匹配原始的单元格值),我认为这是一种攻击,但也可能比其他方法更少语法。我说hack是因为核心枢轴逻辑是重复的,这使得随着查询的发展而更难维护。

​

  1. 在唯一列上执行单个PIVOT,在其他表上联接以生成附加列
    • 这很容易在输出中允许无限多的额外行。PIVOT解析保存我们要在最终结果中显示的多个值的表的ID。

​

让我们首先看一下3,因为这演示了单个枢轴,以及如何为每个枢轴结果包含多个列:

在这个例子中,我允许每个扇区最多有8个结果,需要注意的是,您的必须指定来自PIVOT的所有输出列,它不是动态的。您可以使用动态查询来测试所需的最大列数,并根据这些结果生成以下查询。

还请注意,在这个解决方案中,我们不需要在template13_vw源查询中加入PIVOT表,而是加入了结果,这就是为什么支点返回m_ticker (我认为它是键),而不是在最终结果中显示的ticker。

代码语言:javascript
复制
-- 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并不重要,因此,当聚合或附加公共列对于不同的结果集可能有所不同时,请使用这种语法。

代码语言:javascript
复制
;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
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56676689

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档