Can a query that uses the same filtered table for both unpivoting and joining be written without repetition?

Question

Consider the following query:

create function unpivoter(@TableID int)
returns table
as
return
(
    select a, b
    from
    (
        select foo1, foo2, foo3, foo4
        from table1
        where table1.id = @TableID
    ) tab_in
    unpivot
       (a for b in (foo1, foo2, foo3, foo4)) tab_out

    union all

        select t3.a, t3.b,
        from table1 t1
        join t2 on t1.id = t2.id
        join t3 on t3.id = t2.id
        where t1.id = @TableID

    union all
    
        select t4.a, t4.b,
        from table1 t1
        join t4 on t4.id = t4.id
        where t1.id = @TableID
)

this is obviously highly repetitive. Two things stand out

table1 is given the same filter thrice. I'd replace it with a CTE, but the select list is different between the first use the rest, so this could have a big performance hit depending on the indexing situation.
The union alls all look very similar. The fact that I could have also written the unpivot as a set of three union alls suggests that the two union alls that are currently within the query should somehow be merged in to the unpivot.

Is there anything in T-SQL that can tackle these two problems?

"I'd replace it with a CTE, but the select list is different between the first use the rest, so this could have a big performance hit depending on the indexing situation" - No that isn't correct. It is fine to just have a CTE with `SELECT * FROM table1 WHERE id = @TableID` and then reference that CTE multiple times and SQL Server will sort it out and use the most appropriate indexing for the requirements per reference. The CTE references are inlined as I thought you knew from previous questions — Martin Smith, Aug 25 '23 at 11:36
@MartinSmith Excellent point. Clearly, I have more to learn. — J. Mini, Aug 25 '23 at 15:00
SQL is a declarative language ; you use the syntax to define the problem, not the mechanism to solve it (the execution plan is the cost based optimiser's solution). Don't treat SQL like an imperative language. — MatBailie, Aug 31 '23 at 06:36

Charlieface · Accepted Answer · 2023-08-25T11:54:31.847

4

Not entirely, however you can avoid looking up table1 multiple times by putting the UNION ALL in an APPLY.

Use a (VALUES constructor instead of UNPIVOT, it is far more flexible.

CREATE OR ALTER FUNCTION dbo.unpivoter (@TableID int)
RETURNS TABLE
AS RETURN

SELECT
  u.a,
  u.b
FROM table1 t1

CROSS APPLY (
    SELECT
      tab_out.a,
      tab_out.b
    FROM (VALUES
        ('foo1', t1.foo1),
        ('foo2', t1.foo2),
        ('foo3', t1.foo3),
        ('foo4', t1.foo4)
    ) tab_out(a, b)

    UNION ALL

    SELECT
      t3.a,
      t3.b,
    FROM t2
    JOIN t3 ON t3.id = t2.id
    WHERE t2.id = t1.id

    UNION ALL

    SELECT
      t4.a,
      t4.b,
    FROM t4
    WHERE t4.id = t1.id
) u
WHERE t1.id = @TableID;

You could also replace the VALUES with a few UNION ALL, bu it's more verbose.

CROSS APPLY (
    SELECT
      'foo1' AS a,
      t1.foo1 AS b

    UNION ALL

    SELECT
      'foo2' AS a,
      t1.foo2 AS b

    UNION ALL

    SELECT
      'foo3' AS a,
      t1.foo3 AS b

    UNION ALL

    SELECT
      'foo4' AS a,
      t1.foo4 AS b

    UNION ALL

edited Aug 25 '23 at 11:54

answered Aug 25 '23 at 11:24

Charlieface

52,284
6
19
43

Damn, nice, you beat me by one second :) – siggemannen Aug 25 '23 at 11:27
Very very good. I had a feeling that `CROSS APPLY` would do the trick. It might be worth spelling out in this answer why the same trick won't work with `UNPIVOT`. I have reasonable guesses, but it seems best for a "prefer `CROSS APPLY` to `UNPIVOT`" answer to spell out why `UNPIVOT` can't solve the problem. – J. Mini Aug 25 '23 at 15:03
It's not strictly true that you can't use `UNPIVOT`, you can put *that* into the `APPLY` https://dbfiddle.uk/-ERoq_rW note the lack of a `FROM` in the subquery. – Charlieface Aug 25 '23 at 17:19
Unqualified references in sub-queries just confuses people, especially those newer to SQL. – MatBailie Aug 31 '23 at 13:31
In the fiddle with the unpivot – MatBailie Aug 31 '23 at 14:01
Ah sorry I kind of dashed that off – Charlieface Aug 31 '23 at 14:26

score 0 · Answer 2 · answered Aug 31 '23 at 06:28

CREATE FUNCTION unpivoter(@TableID INT)
RETURNS TABLE
AS
RETURN
(
    WITH FilteredTable AS (
        SELECT foo1, foo2, foo3, foo4
        FROM table1
        WHERE table1.id = @TableID
    ),
    UnpivotedData AS (
        SELECT a, b
        FROM FilteredTable
        UNPIVOT (
            a FOR b IN (foo1, foo2, foo3, foo4)
        ) AS u
    ),
    JoinedData AS (
        SELECT t3.a, t3.b
        FROM table1 t1
        JOIN t2 ON t1.id = t2.id
        JOIN t3 ON t3.id = t2.id
        WHERE t1.id = @TableID

        UNION ALL

        SELECT t4.a, t4.b
        FROM table1 t1
        JOIN t4 ON t1.id = t4.id
        WHERE t1.id = @TableID
    )
    SELECT * FROM UnpivotedData
    UNION ALL
    SELECT * FROM JoinedData
);

In the above code I have used three CTEs: FilteredTable, UnpivotedData, and JoinedData. The FilteredTable CTE filters the necessary data from table1 once and is used by both the unpivoting and joining parts of the query. The UnpivotedData CTE performs the unpivoting operation. The JoinedData CTE combines the two UNION ALL blocks.

Can a query that uses the same filtered table for both unpivoting and joining be written without repetition?

2 Answers2