Search code examples
sqldb2ibm-midrangedb2-400

How to transpose ROW to Column in DB2

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:

F1 F2 F3
ABC DEF GHI

to:

F1
ABC
DEF
GHI
Solution

  • 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.