将行作为列返回

发布于 2024-11-29 11:35:48 字数 690 浏览 3 评论 0原文

我很想将其作为单个查询来完成，但我认为它需要一个游标。如果我不能将其作为单个查询来执行，我想将结果作为 SYS_REFCURSOR 输出。简化示例：

PRODUCT

ID   NAME         Part Number   SKU
------------------------------------
1    Widgetizer     150         1001
2    Widgetizer200  200         1002
3    WidgetizerDlx  250         1003

PRODUCT_SPEC

P_ID   NAME     VALUE
----------------------------
1      WEIGHT    5
1      HEIGHT    10
1      VERSION   1
1      COLOR     RED
2      WEIGHT    7
2      HEIGHT    10
2      VERSION   2
2      COLOR     BLUE

查询：

对于每个 SKU 小于 1003 的产品，返回：

Product name, part #, SKU, WEIGHT, HEIGHT, COLOR

原文

I'd love to do it as a single query, but I think it's going to require a cursor. If I can't do it as a single query, I'd like to output the results as a SYS_REFCURSOR. A simplified example:

PRODUCT

ID   NAME         Part Number   SKU
------------------------------------
1    Widgetizer     150         1001
2    Widgetizer200  200         1002
3    WidgetizerDlx  250         1003

PRODUCT_SPEC

P_ID   NAME     VALUE
----------------------------
1      WEIGHT    5
1      HEIGHT    10
1      VERSION   1
1      COLOR     RED
2      WEIGHT    7
2      HEIGHT    10
2      VERSION   2
2      COLOR     BLUE

Query:

For each product with SKU less than 1003, return:

Product name, part #, SKU, WEIGHT, HEIGHT, COLOR

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葮薆情 2024-12-06 11:35:49

通常称为枢轴查询。

select p.name, p.partnum, p.sku,
       max( case when s.name='WEIGHT' then s.value else null end ) weight,
       max( case when s.name='HEIGHT' then s.value else null end ) height,
       max( case when s.name='COLOR'  then s.value else null end ) color
from product p join product_spec s on p.id = s.p_id
where sku <= 1003
group by p.name, p.partnum, p.sku

Generally referred to as a pivot query.

select p.name, p.partnum, p.sku,
       max( case when s.name='WEIGHT' then s.value else null end ) weight,
       max( case when s.name='HEIGHT' then s.value else null end ) height,
       max( case when s.name='COLOR'  then s.value else null end ) color
from product p join product_spec s on p.id = s.p_id
where sku <= 1003
group by p.name, p.partnum, p.sku

回复收藏 0 原文

~没有更多了~