Is there a way to have a sql query loop through an array and stack each view returned into one view?-CodePudding

I have SQL table like the below in an oracle DB:

I would like to obtain the below view from the above table:

I am able to produce 1 row of the view with the below query (in this example Item_id 'a').

SELECT
    Item_ID,
    transaction_date as Latest_transaction
FROM
    (
        SELECT
            *
        FROM
            TABLE
        WHERE
            Item_id LIKE '%a%'
        ORDER BY
            transaction_date DESC
    )
WHERE
    ROWNUM = 1

I would like to perform the following query on each on each value in the array ['a', 'b' , 'd' , 'e' , 'g' , 'z' ] and then append each row to a view via a UNION. However, I am unsure how to do this since SQL is not able to do FOR loops.

I have tried running a giant query with a union for each ID, but in my actual use case there are too many Item_IDs(~4k) for SQL to execute this query.

SELECT
    Item_ID,
    transaction_date as Latest_transaction
FROM
    (
        SELECT
            *
        FROM
            TABLE
        WHERE
            Item_id LIKE '%a%'
        ORDER BY
            transaction_date DESC
    )
WHERE
    ROWNUM = 1
UNION
SELECT
    Item_ID,
    transaction_date as Latest_transaction
FROM
    (
        SELECT
            *
        FROM
            TABLE
        WHERE
            Item_id LIKE '%b%'
        ORDER BY
            transaction_date DESC
    )
WHERE
    ROWNUM = 1
...con't for all IDs.

CodePudding user response：

You can accomplish this with a CTE and regexp_substr:

with transactions as
(
    select regexp_substr(tbl.Item_ids, '[^;] ', 1, level) Item_id
        , tbl.Transaction_date
    from tbl
    connect by level < length(replace(tbl.Item_ids, ';', ''))   1
)
select Item_id
    , max(Transaction_date) Latest_transaction
from transactions
group by Item_id
order by Item_id

regexp_substr will allow you to split a string into a new row based on a delimiter, we then take those rows and find the max Transaction_date.

However, this will only give you the characters from Item_ids as I am assuming that is the "array" you are talking about. If you can clarify where the g result is coming from, I can include that in my answer as well.

CodePudding user response：

WITH data(transaction_date, item_ids) AS (
    SELECT TO_DATE('10/11/2022','MM/DD/YYYY'), 'a;b;z' FROM DUAL UNION ALL
    SELECT TO_DATE('10/10/2022','MM/DD/YYYY'), 'a;d' FROM DUAL UNION ALL    
    SELECT TO_DATE('10/9/2022','MM/DD/YYYY'), 'a;b;d;z' FROM DUAL UNION ALL       
    SELECT TO_DATE('10/8/2022','MM/DD/YYYY'), 'z;e' FROM DUAL 
),
all_ids(id) AS (
    SELECT regexp_substr('a;b;d;e;g;z','[^;] ',1,LEVEL) FROM DUAL
    CONNECT BY regexp_substr('a;b;d;e;g;z','[^;] ',1,LEVEL) IS NOT NULL
),
expanded_ids AS (
    SELECT id, MAX(transaction_date) AS latest_transaction FROM (
        SELECT transaction_date, regexp_substr(item_ids,'[^;] ',1,LEVEL) AS id FROM data
        CONNECT BY regexp_substr(item_ids,'[^;] ',1,LEVEL) IS NOT NULL
            AND PRIOR transaction_date = transaction_date AND PRIOR sys_guid() IS NOT NULL
    )
    GROUP BY id
)
SELECT a.id, e.latest_transaction 
FROM all_ids a 
LEFT JOIN expanded_ids e ON e.id = a.id
ORDER BY id
;