Oracle将行数据作为指定数量的唯一条目的列返回 [英] Oracle return row data as columns for specified number of unique entries

问题描述

我有一个Oracle表，该表按项目编号标识符用作项目的活动日志/更改跟踪器.我想进行最后三个更改，并以列格式列出它们.

I have an Oracle table that functions as an activity log/change tracker for items by their item number identifier. I would like to take the last three changes and list them out in a column format.

样本数据

Item_No   Shipping_date     Last_updated
100       01-Sep-16         24-Aug-16
101       10-Sep-16         24-Aug-16
102       31-Aug-16         24-Aug-16
101       11-Sep-16         25-Aug-16
101       12-Sep-16         26-Aug-16
100       31-Aug-16         27-Aug-16
102       01-Sep-16         27-Aug-16
103       01-Oct-16         27-Aug-16

Expected output would be:
Item_No Shipping_1  Updated_1  Shipping_2  Updated_2  Shipping_3  Updated_3
100     01-Sep-16   24-Aug-16  31-Aug-16   27-Aug-16
101     10-Sep-16   24-Aug-16  11-Sep-16   25-Aug-16  12-Sep-16   26-Aug-16
102     31-Aug-16   24-Aug-16  01-Sep-16   27-Aug-16
103     01-Oct-16   27-Aug-16

有人有什么想法吗?我已经弄乱了PIVOT，DECODE，但似乎什么也无法工作.我最接近的是:

Anyone have any ideas? I've messed around with PIVOT, DECODE, but I can't seem to get anything to work. The closest I've come is this:

SELECT
  ITEM_NO,
  LISTAGG(TO_CHAR(Shipping_date, 'DD-MON-YY') || ' ' || TO_CHAR(Last_updated, 'DD-MON-YY'))
  WITHIN GROUP
  (ORDER BY Last_updated) Shipping_date
FROM SHIPPING_DETAIL
GROUP BY Item_No
HAVING COUNT(*) > 1

推荐答案

Oracle PIVOT可以工作:

Oracle PIVOT can work:

with test_data (item_no, shipping_date, last_updated) as (
SELECT 100, to_date(       '01-Sep-16','DD-MON-YY'), to_date('24-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 101, to_date(       '10-Sep-16','DD-MON-YY'), to_date('24-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 102, to_date(       '31-Aug-16','DD-MON-YY'), to_date('24-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 101, to_date(       '11-Sep-16','DD-MON-YY'), to_date('25-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 101, to_date(       '12-Sep-16','DD-MON-YY'), to_date('26-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 100, to_date(       '31-Aug-16','DD-MON-YY'), to_date('27-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 102, to_date(       '01-Sep-16','DD-MON-YY'), to_date('27-Aug-16','DD-MON-YY') FROM DUAL UNION ALL
SELECT 103, to_date(       '01-Oct-16','DD-MON-YY'), to_date('27-Aug-16','DD-MON-YY') FROM DUAL 
)SELECT item_no,
       event_1_shipping,
       event_1_last_updated,
       event_2_shipping,
       event_2_last_updated,
       event_3_shipping,
       event_3_last_updated
FROM   (SELECT last_3.item_no,
               last_3.shipping_date,
               last_3.last_updated,
               ROW_NUMBER () OVER (PARTITION BY item_no ORDER BY filter_rown DESC) rown
        FROM   (SELECT td.*,
                       ROW_NUMBER () OVER (PARTITION BY item_no ORDER BY last_updated DESC) filter_rown
                FROM   test_data td) last_3
        WHERE  filter_rown <= 3) PIVOT (MIN (shipping_date) "SHIPPING", MIN (last_updated) "LAST_UPDATED"
                                 FOR rown
                                 IN (1 AS "EVENT_1", 2 AS "EVENT_2", 3 AS "EVENT_3"))

***从我的原始答案中进行了编辑，以允许将事件从最早到最新进行排序.

*** Edited from my original answer to allow the events to be sorted from earliest to latest.

这篇关于Oracle将行数据作为指定数量的唯一条目的列返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！