我们需要使用多行和多列,并将它们转换为每键1行。我有一个枢轴查询,但它不起作用。我得到了一些关于“列定义不明确”的错误
我们的数据如下:
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行,每个代码/公司都在一个不同的列中。因此,输出看起来应该是:
5 ADNT Adient PLC AUTO Autobytel Inc. THRM Gentherm Inc........你明白了吧。每个ID 1行,以及它自己的列中的彼此值。我尝试的查询是:
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;发布于 2019-06-20 01:47:19
首先要了解枢轴,您可以在结果集中选择一列作为PIVOT锚点,即数据将围绕的铰链,这在FOR子句中指定。
您只能使用
PIVOTFOR一列,但您可以在子查询中或从联接或视图中构造该列作为目标数据查询,OP使用了ROW_NUMBER(),但您可以使用任何您希望使用的SQL机制,甚至可以使用CASE语句来构建自定义列,以便在数据集中没有自然列可使用时进行转换。
PIVOT将为FOR列中的每个值创建一个列,并为该列提供您指定的聚合函数的值。
它有助于可视化构造的记录集,在应用支点之前,下面的SQL可以重新创建OP提供的数据场景。这里我使用了表变量来代替OPs表和视图。
-- 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和company ),所以我们必须使用以下技术之一:
PIVOT查询并加入结果
PIVOT,在其他表上联接以生成附加列PIVOT解析保存我们要在最终结果中显示的多个值的表的ID。
让我们首先看一下3,因为这演示了单个枢轴,以及如何为每个枢轴结果包含多个列:
在这个例子中,我允许每个扇区最多有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.sectorhttps://stackoverflow.com/questions/56676689
复制相似问题