How to call the result of an aggregation function within a pivot block-CodePudding

The following example is only used to understand why I want to do what I'm doing. But the second example is better to understand the problem.

I want to create a table. Each column represent a value that should be in the table.

WITH
    FUNCTION f(arg INTEGER, colum_name VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        IF arg = 0
        THEN
            RETURN 'this column doesn''t exist';
        ELSE
            RETURN colum_name;
        END IF;
    END;
    t (a) AS (SELECT COLUMN_VALUE FROM sys.odcivarchar2list ('a', 'b', 'd'))
SELECT *
  FROM (SELECT a FROM t)
       PIVOT (f(COUNT (*),a)
             FOR a
             IN ('a', 'b', 'c', 'd'));

What it should return:

a	b	c	d
a	b	this column doesn' t exist	d

Because I need the name of the column, I can't make a subquery that takes the result of the aggregation function and uses f in the principal query.

Now the second example:

the first query counts every lines with the value 1 and then the value 2.

SELECT * FROM (SELECT 1 a FROM DUAL) PIVOT (COUNT (*)       FOR a IN (1, 2));

It's working.

But this query doesn't work. count(*) 1 isn't considered as a aggreagation functon

SELECT *
  FROM (SELECT 1 a FROM DUAL) PIVOT (COUNT (*)          1 FOR a IN (1, 2));

[Error] Execution (40: 53): ORA-56902: expect aggregate function inside pivot operation

I can't do that ouside the pivot because: bold text in the first example.

to test the code:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8ea8a78038fbadddb417c330f0d33314

----------------------------- This part was added after MTO gived an answer:

WITH
  FUNCTION f(
    arg        IN INTEGER,
    colum_name IN VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF arg = 0 THEN
      RETURN 'this column doesn''t exist';
    ELSE
      RETURN colum_name;
    END IF;
  END;
  t (a) AS (
    SELECT COLUMN_VALUE
    FROM   sys.odcivarchar2list ('a', 'b', 'd')
  )
SELECT f(a, 'a') AS a,
       f(b, 'b') AS b,
       f(c, 'c') AS c,
       f(d, 'd') AS d
FROM   t
PIVOT (
  COUNT (*)
  FOR a IN (
    'a' AS a,
    'b' AS b,
    'c' AS c,
    'd' AS d
  )
);

It's working but:

But In the real case I have a lot of column, I would like to avoid to rewrite them all.
And I would like to avoid making typing error. writing 2 time the same name is prone making typing error.

CodePudding user response：

It will NOT work inside the PIVOT you MUST call the function outside of the PIVOT:

WITH
  FUNCTION f(
    arg        IN INTEGER,
    colum_name IN VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF arg = 0 THEN
      RETURN 'this column doesn''t exist';
    ELSE
      RETURN colum_name;
    END IF;
  END;
  t (a) AS (
    SELECT COLUMN_VALUE
    FROM   sys.odcivarchar2list ('a', 'b', 'd')
  )
SELECT f(a, 'a') AS a,
       f(b, 'b') AS b,
       f(c, 'c') AS c,
       f(d, 'd') AS d
FROM   t
PIVOT (
  COUNT (*)
  FOR a IN (
    'a' AS a,
    'b' AS b,
    'c' AS c,
    'd' AS d
  )
);

Which outputs:

A B C D

a b this column doesn't exist d

A	B	C	D
a	b	this column doesn't exist	d

Addressing the comments:

Because I need the name of the column, I can't make a subquery that takes the result of the aggregation function and uses f in the principal query.

Yes, you can; exactly as the example above shows.

An SQL statement (in any dialect, not just Oracle) must have a known, fixed number of output columns before it can be compiled. Therefore, you can know all the columns that will be output from the PIVOT statement (one for each item in the FOR clause and one for each column from the original table that is not used in the PIVOT clause) and can call the function on those columns you want to.

And I would like to avoid making typing error. writing 2 time the same name is prone making typing error.

Perform a code review on your code after writing it and use testing to check that it works properly.

Your second query can be fixed using exactly the same principle (except you need quoted identifiers as, without explicitly providing identifiers, the identifier created by the pivot start with numbers and must be quoted):

SELECT "1"   1 AS "1",
       "2"   1 AS "2"
FROM   (SELECT 1 a FROM DUAL)
PIVOT (COUNT (*) FOR a IN (1, 2));

Which outputs:

1 2

2 1

1	2
2	1

db<>fiddle here

CodePudding user response：

This solution allow to get the names of elements inside the table, without having to write these elements 2 times. The elements that are not in the table have the value null. If you want to change use something other than the null, you have to use this query as a sub-query and use the function nvl. But you have to rewrite the name of all the columns again.... This solution suits me, but it's not totally satisfactory...

with
    t (a) AS (SELECT COLUMN_VALUE FROM sys.odcivarchar2list ('a', 'b', 'd'))
SELECT *
  FROM t
       PIVOT (max(distinct a)
             FOR a
             IN ('a', 'b', 'c', 'd'));

code