Why does the IN Statement within the PIVOT clause has to be static

Question

Everytime I use the pivot clause within SQL, I wonder why the IN Statement has to be static and I need to write something like 'A', 'B'.

SELECT *
FROM
(
    SELECT key_1, column_name, value_1
    FROM table_1
)
PIVOT
(
    MAX(column_name)
    FOR column_name in('A', 'B')
)

Is there a technical reason why it's not allowed to write something like

SELECT *
FROM
(
    SELECT key_1, column_name, value_1
    FROM table_1
)
PIVOT
(
    MAX(column_name)
    FOR column_name in(SELECT value_1 FROM table_2)
)

Because it would be obviously a lot more comfortable to write the IN query dynamic.

Thanks for your answers!

In general, a SQL query needs to specify its columns explicitly. You would use dynamic SQL if you need more flexibility. — Gordon Linoff, Sep 02 '19 at 13:52

score 0 · Answer 1 · answered Sep 02 '19 at 13:57

I will rewrite your first pivot query to something which makes more sense:

SELECT *
FROM
(
    SELECT key_1, column_name
    FROM table_1
)
PIVOT
(
    COUNT(column_name)
    FOR column_name in('A', 'B')
)

This query is logically equivalent to the following:

SELECT
    key_1,
    COUNT(CASE WHEN column_name = 'A' THEN column_name END) AS a_count,
    COUNT(CASE WHEN column_name = 'B' THEN column_name END) AS b_count
FROM table_1
GROUP BY
    key_1;

By allowing the IN clause of the PIVOT query to be a dynamic result set, it would no longer be possible to express the query using static SQL syntax. Instead, it would require dynamic SQL. While it is certainly possible to use dynamic SQL to cover your requirement, it is not supported by the PIVOT operator.

score 0 · Accepted Answer · answered Sep 02 '19 at 14:03

0

It is a common requirement in SQL that a SQL query must return a fixed set of columns. You basically are expected to know beforehand how many columns your query will return.

Keep in mind that, for any database API, the first step that is performed when running a SQL query is to prepare the statement. This operation generates a statement handler that is returned to the client, and that it can use to allocate the data structure that will later on store the results of the query. Since the same statement may be executed with different bind values, this implies that a given query should always return the same, fixed number of columns.

answered Sep 02 '19 at 14:03

GMB

216,147
25
84
135

Hello GMB, i take it. Sounds comprenhensible. I searched in the internet for a detailed information about what's the database doing exactly in this step but did not find anything. Can you provide me a link for additional information? That would be great! – Rene Moch Sep 03 '19 at 08:16
@ReneMoch: I would suggest the following reading: https://community.oracle.com/thread/2422880?tstart=2 – GMB Sep 03 '19 at 08:53

Why does the IN Statement within the PIVOT clause has to be static

2 Answers2