Oracle动态透视SQL

zujrkrfu 于 2023-04-29 发布在 Oracle

关注(0)|答案(2)|浏览(128)

我的表：

CREATE TABLE tbl(
id int,
fiber varchar2(40),
pct int);

insert into tbl (id, fiber, pct) values ('1','Cotton','60');
insert into tbl (id, fiber, pct) values ('1','Poly','40');
insert into tbl (id, fiber, pct) values ('2','Elastane','30');
insert into tbl (id, fiber, pct) values ('2','Cotton','50');
insert into tbl (id, fiber, pct) values ('2','Spandex','20');

我尝试用PIVOT编写一个SQL查询，将这些内容放入列中，因此我的SQL结果如下所示：

ID  Fiber1   Pct1   Fiber2   Pct2   Fiber3   Pct3
-------------------------------------------------
1   Cotton   60     Poly     40
2   Elastane 30     Cotton   50     Spandex  20

我以前从未使用过Pivot。我找到的所有示例都在Pivot查询中显式列出了列。问题是，fiber列有几十个不同的值，因此在查询中对它们进行硬编码是不切实际的。
我很好奇，是否有一种方法可以像这样使列通用，并将数据放入行中。
谢谢大家！

oracle

来源：https://stackoverflow.com/questions/76132410/oracle-dynamic-pivot-sql

2条答案

按热度按时间

jjhzyzn01#

使用ROW_NUMBER()解析函数对每个id和PIVOT的行进行编号：

SELECT id,
       "1_FIBER" AS fiber1,
       "1_PCT" AS pct1,
       "2_FIBER" AS fiber2,
       "2_PCT" AS pct2,
       "3_FIBER" AS fiber3,
       "3_PCT" AS pct3
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY fiber) AS rn
  FROM   tbl t
)
PIVOT (
  MAX(fiber) AS fiber,
  MAX(pct) AS pct
  FOR rn IN (1, 2, 3) 
)

或者，使用ROW_NUMBER()解析函数和条件聚合：

SELECT id,
       MAX(CASE rn WHEN 1 THEN fiber END) AS fiber1,
       MAX(CASE rn WHEN 1 THEN pct END) AS pct1,
       MAX(CASE rn WHEN 2 THEN fiber END) AS fiber2,
       MAX(CASE rn WHEN 2 THEN pct END) AS pct2,
       MAX(CASE rn WHEN 3 THEN fiber END) AS fiber3,
       MAX(CASE rn WHEN 3 THEN pct END) AS pct3
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY fiber) AS rn
  FROM   tbl t
)
GROUP BY id;

对于样本数据，两者都输出：
| ID|纤维1|PCT1|纤维2|PCT2|FIBER3|PCT3|
| --------------|--------------|--------------|--------------|--------------|--------------|--------------|
| 1|棉花|六十|聚乙烯|四十|联系我们|联系我们|
| 二|棉花|五十|弹性纤维|三十|氨纶|二十|
fiddle

赞(0）回复(0）举报 2023-04-29

8ftvxx2r2#

使用row_number()为每一行生成行号，然后使用group by、max()和case子句进行条件聚合，如下所示：

with cte as (
  select ID, FIBER, PCT, row_number() over (partition by ID order by null) as rn 
  from tbl
)
select ID, max(case when rn = 1 then FIBER END) as fiber1,
           max(case when rn = 1 then PCT END) as Pct1,
           max(case when rn = 2 then FIBER END) as Fiber2,
           max(case when rn = 2 then PCT END) as Pct2,
           max(case when rn = 3 then FIBER END) as Fiber3,
           max(case when rn = 3 then PCT END) as Pct3
from cte
group by ID;

如果您对按行级别排序数据感兴趣，则可以使用order by fiber或其他列
Demo here

赞(0）回复(0）举报 2023-04-29

我来回答

Oracle动态透视SQL

2条答案

相关问题

热门标签

最新问答