in DB2, how can we transpose a simple “select * from TABLE fetch first 1 rows only” query output row into column?

TABLE name could be dynamic so (*) in select is a must..

TIA for ur inputs and suggestion.

Example:

from:

to:

CodePudding user response：

Short answer is you can't.
There's nothing in Db2 for IBM i that will do this with SELECT * and a dynamic table.

Long answer, you can build a stored procedure or user defined table function that dynamically builds and executes an old school statement that looks like so:

with firstRow as 
  (select F1, F2, F3 from table fetch first row only)
select F1
from firstRow
UNION ALL 
select F2
from firstRow
UNION ALL
select F3
from firstRow;

Alternately, since you're on v7.4, you could build and execute a dynamic statement that CONCAT the fields into string list and then use the SPLIT() table function to deconstruct the the list into rows.

Lastly, you might be able to build and execute a dynamic statement that uses the JSON functions to build a JSON array which could then be deconstructed into rows with the JSON_TABLE() function.

But as emphasized, in all cases you'll need to know column and table names for the actual SELECT. Thus the need to dynamically build the statement.

CodePudding user response：

Try a generic table function like below.
The 1-st parameter is whatever valid SELECT statement text.
The 2-nd parameter is a list of column names of this SELECT statement to convert.

CREATE OR REPLACE FUNCTION MYSCHEMA.UNPIVOT (P_STMT VARCHAR (32000), P_COLLIST VARCHAR (100))
RETURNS TABLE (C VARCHAR (100))
BEGIN
    DECLARE SQLCODE INT;
    DECLARE V_VAL VARCHAR (100);
    DECLARE C1 CURSOR FOR S1;

    PREPARE S1 FROM 'SELECT V.C_ FROM (' || P_STMT || '), TABLE (VALUES ' || P_COLLIST || ') V (C_)';
    OPEN C1;
    L1: LOOP
        FETCH C1 INTO V_VAL;
        IF SQLCODE = 100 THEN LEAVE L1; END IF;
        PIPE (V_VAL);
    END LOOP L1;
    CLOSE C1;
    RETURN;
END
@

You must either:

construct the SELECT statement to return only string columns and specify a simple column list:

SELECT * 
FROM TABLE (MYSCHEMA.UNPIVOT (
    'SELECT * FROM (VALUES (CHAR (1), ''2'', CHAR (CURRENT DATE))) T (C1, C2, C3)'
  , 'C2, C3'
)) T

or explicitly cast every non-string column in the list of columns to CHAR:

SELECT * 
FROM TABLE (MYSCHEMA.UNPIVOT (
    'SELECT * FROM (VALUES (1, ''2'', CURRENT DATE)) T (C1, C2, C3)'
  , 'CHAR (C1), CHAR (C3)'
)) T