ibm-midrange

Is there a way to transpose a data in WRKQRY in SMART400?

I have the data:

T1 A1
T1 A2
T1 A3
T2 B1
T2 B2

Is it possible to make it like this using WRKQRY:

T1 A1 A2 A3
T2 B1 B2
Solution

  • You don't indicate rather how many columns are in the output table. So I don't know if you're trying to do

    1. aggregration
      ('T1', 'A1 A2 A3')
      ('T2', 'B1 B2')

    2. pivot
      ('T1', 'A1', 'A2', 'A3')
      ('T2', 'B1', 'B2', - )

    But in either case, the simple answer is NO. WRKQRY doesn't support either.

    SQL based tools do support both options.

    Aggregation is easy assuming a supported version of the OS with the listagg() function available. On an older version of the OS you could build your own similar function.

    The listagg() function could be used in an SQL view which you could then query with WRKQRY.

    Pivoting the data is more difficult, as Db2 for IBM i doesn't have a built in PIVOT function. It can be done, especially with a small discreet set of values.

    That may be possible to embed in an SQL view.

    But with a set of values that are unknown till run time, you have have to do things dynamically/programmatically.