PL SQL: group by, drop NULL, drop duplicates, save to list

Question

I need to do something like:

def first_items(x: pd.Series):
    """Helper function for items those aren't NA."""
    arr = x.values
    arr = np.unique(arr[~pd.isna(arr)])
    if not len(arr):
        return np.nan
    elif len(arr) == 1:
        return arr[0]
    else:
        return arr

tmp.groupby(groups).agg(first_items)

I am interested in doing that in PL SQL. How can I do that?

There may be Python developers who also know Oracle SQL and can translate whatever this does, but for the rest of us some sample data and expected results would be helpful. Why do you need PL/SQL and not just SQL? How is PL/SQL Developer related to the question? — William Robertson, Sep 07 '22 at 12:46

score 0 · Answer 1 · answered Sep 07 '22 at 22:18

If you mean you want to take an array named, say, 'strings' containing:

'A', 'B', '', 'A', 'C', ''

and you want to deduplicate it and remove empty values to leave

'A', 'B', 'C'

then the PL/SQL syntax for that would be:

strings := set(strings) multiset except stringtab('', null);

dbFiddle

If you mean something else, then please provide sample data and expected results.

PL SQL: group by, drop NULL, drop duplicates, save to list

1 Answers1