X
X
Xeldos2020-12-10 11:22:48
SQL
Xeldos, 2020-12-10 11:22:48

How to join two subqueries and then aggregate the result of one of them?

Colleagues, I'm stupid. Help cross the hedgehog and snake.
I need to get a set of features for each row from the data set, and combine these features into one row. Seems like a standard issue.
Option 1:

WITH
    cte1 as (SELECT * FROM get_data()),
    cte2 as (
        SELECT
            flag
        FROM
            cte1
            CROSS APPLY check_flag(cte1.key) as checker
        WHERE
            checker.filter=1
    )
SELECT
    cte1.*, stuff((SELECT ','+flag FROM cte2 FOR XML PATH ''), 1, 1, '')

Not good as there is no connection between subrequests and I always get the full set of flags.
Option 2:
WITH
    cte1 as (SELECT * FROM get_data()),
    cte2 as (
        SELECT
            flag, cte1.key
        FROM
            cte1
            CROSS APPLY check_flag(cte1.key) as checker
        WHERE
            checker.filter=1
    )
SELECT
    cte1.*,
    stuff((SELECT ','+flag FROM cte2 WHERE cte1.key = cte2.key FOR XML PATH ''), 1, 1, '')

Not good, since the request takes 30 seconds (although this is good, there was another option that was considered 2 minutes). It is clear why 30 seconds, the connection goes in the SELECT clause, that is, I am essentially filtering a hefty Cartesian product.
How would I connect two subqueries in a normal way, via FROM, and then aggregate the result?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
X
Xeldos, 2020-12-10
@Xeldos

SELECT gd.*, STUFF (t.flags, 1, 1, '')
FROM get_data() gd
CROSS APPLY (
    SELECT ',' + flag
    FROM check_flag(gd.key)
    WHERE filter = 1
    FOR XML PATH ('')
) t(flags)

Thank you unfilled

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question