If I have a query returning PARAM_VALUE with multiple values like

How can I return multiple columns on demand using SELECT to present this data like

CodePudding user response：

SQL (not just Oracle) requires a known, fixed number of output columns. So, if you have a fixed maximum number of delimited values in your string then you can use string functions to find the sub-strings:

SELECT param_value,
       CASE pos1
       WHEN 0
       THEN param_value
       ELSE SUBSTR(param_value, 1, pos1 - 1)
       END AS param_value1,
       CASE 
       WHEN pos1 = 0
       THEN NULL
       WHEN pos2 = 0
       THEN SUBSTR(param_value, pos1   1)
       ELSE SUBSTR(param_value, pos1   1, pos2 - pos1 - 1)
       END AS param_value2,
       CASE 
       WHEN pos2 = 0
       THEN NULL
       WHEN pos3 = 0
       THEN SUBSTR(param_value, pos2   1)
       ELSE SUBSTR(param_value, pos2   1, pos3 - pos2 - 1)
       END AS param_value3,
       CASE 
       WHEN pos3 = 0
       THEN NULL
       WHEN pos4 = 0
       THEN SUBSTR(param_value, pos3   1)
       ELSE SUBSTR(param_value, pos3   1, pos4 - pos3 - 1)
       END AS param_value4
FROM   (
  SELECT param_value,
         INSTR(param_value, ',', 1, 1) AS pos1,
         INSTR(param_value, ',', 1, 2) AS pos2,
         INSTR(param_value, ',', 1, 3) AS pos3,
         INSTR(param_value, ',', 1, 4) AS pos4,
         INSTR(param_value, ',', 1, 5) AS pos5
  FROM   table_name
);

or, more compact, but slower, using regular expressions:

SELECT param_value,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 1, NULL, 1) AS param_value1,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 2, NULL, 1) AS param_value2,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 3, NULL, 1) AS param_value3,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 4, NULL, 1) AS param_value4
FROM   table_name;

Which, for the sample data:

create table table_name (param_value) AS
  SELECT NULL FROM DUAL UNION ALL
  SELECT 'A' FROM DUAL UNION ALL
  SELECT 'A,B' FROM DUAL UNION ALL
  SELECT 'A,B,C' FROM DUAL UNION ALL
  SELECT 'A,B,C,D' FROM DUAL UNION ALL
  SELECT 'A,B,C,D,E' FROM DUAL;

Both output:

fiddle

CodePudding user response：

If you want to generate the values for an unknown length of list then generate the values as rows (rather than columns):

SELECT param_value,
       item,
       CASE epos
       WHEN 0
       THEN SUBSTR(param_value, spos)
       ELSE SUBSTR(param_value, spos, epos - spos)
       END AS param
FROM   table_name t
       CROSS APPLY (
         SELECT LEVEL AS item,
                CASE LEVEL
                WHEN 1
                THEN 1
                ELSE INSTR(t.param_value, ',', 1, LEVEL - 1)   1
                END AS spos,
                INSTR(t.param_value, ',', 1, LEVEL) AS epos
         FROM   DUAL
         CONNECT BY INSTR(t.param_value, ',', 1, LEVEL - 1) > 0
       ) b

Which, for the sample data:

create table table_name (param_value) AS
  SELECT NULL FROM DUAL UNION ALL
  SELECT 'A' FROM DUAL UNION ALL
  SELECT 'A,B' FROM DUAL UNION ALL
  SELECT 'A,B,C' FROM DUAL UNION ALL
  SELECT 'A,B,C,D' FROM DUAL UNION ALL
  SELECT 'A,B,C,D,E' FROM DUAL;

Outputs:

If you then want it as columns then transpose the data set in whatever third party application you are using to access the database as it is likely to support the output format you want (whereas it would be very difficult to generate a dynamic output using SQL).

fiddle