英文:
SQL Query, Create new column instead a new row SQL Oracle
问题
尝试创建一个查询以获取所有产品及其相关的日期和包装：
SELECT
p.id,
d.date,
pc.pack
FROM Products p
LEFT JOIN Dates d ON p.id = d.id
LEFT JOIN Packs pc ON p.id = pc.id;
英文:
i try create a query to get all products and your repectives dates and packs:
SELECT
p.id,
d.date,
pc.pack
FROM Products p
LEFT JOIN Dates d ON p.id = d.id
LEFT JOIN Packs pc ON p.id = pc.id;
Result:
But i need the query return a table like this:
Or this:
Tks for attention =]]]]]]]]
EDIT:
ID PRODUCT
1 AAA
2 BBB
3 CCC
4 DDD
ID DATE
1 2022-05-01
1 2023-02-08
2 2021-10-25
3 2020-04-18
3 2021-06-07
3 2023-01-19
4 2022-12-21
ID PACK
1 4
2 6
2 12
3 8
3 16
4 1
4 10
4 20
答案1
得分: 1
你尝试过使用 STRING_AGG 吗？
SELECT p.id, LISTAGG(d.date, ', ') WITHIN GROUP(
ORDER BY
d.date
) AS dates, LISTAGG(pc.pack, ', ') WITHIN GROUP(
ORDER BY
pc.pack
) AS packs
FROM Products p
LEFT JOIN Dates d ON p.id = d.id
LEFT JOIN Packs pc ON p.id = pc.id
GROUP BY p.id;
英文:
Have you tried to use STRING_AGG?
SELECT p.id, LISTAGG(d.date, ', ') WITHIN GROUP(
ORDER BY
d.date
) AS dates, LISTAGG(pc.pack, ', ') WITHIN GROUP(
ORDER BY
pc.pack
) AS packs
FROM Products p
LEFT JOIN Dates d ON p.id = d.id
LEFT JOIN Packs pc ON p.id = pc.id
GROUP BY p.id;
答案2
得分: 1
使用LISTAGG的DISTINCT可以解决问题。
LISTAGG聚合函数通过使用新的DISTINCT支持去重。
由于您不希望有重复的包/日期，以下是如何实现的：
with cte as (
SELECT p.id, d.date_, pc.pack
FROM products p
LEFT JOIN dates d ON p.id = d.id
LEFT JOIN packs pc ON p.id = pc.id
)
select id,
listagg(DISTINCT date_, ', ' on overflow truncate with count)
within group (order by date_) dates,
listagg(DISTINCT pack, ', ' on overflow truncate with count)
within group (order by pack) packs
from cte
group by id;
英文:
Using LISTAGG DISTINCT can do the trick.
The LISTAGG aggregate function supports duplicate elimination by using the new DISTINCT.
Since you dont want duplicated packs/dates this is how to do it :
with cte as (
SELECT p.id, d.date_, pc.pack
FROM products p
LEFT JOIN dates d ON p.id = d.id
LEFT JOIN packs pc ON p.id = pc.id
)
select id,
listagg (DISTINCT date_,', ' on overflow truncate with count)
within group (order by date_) dates,
listagg (DISTINCT pack,', ' on overflow truncate with count)
within group (order by pack) packs
from cte
group by id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论