Re: SQL to transform Rows to Columns

The tables really need to be /corrected/, and if not, separate SQL requests in their own modules probably will be the better choice; i.e. if row_oriented_db then call procABC_rows else call procABC_cols. Although the request is an effective pivot query, those are typically used for reporting selected data, rather than for selecting data.

Note that unless ordering the rows to match the layout of the columns is possible, there will be no ability for ensuring valid selection equivalence between data as rows and data as columns. A sequence number in the "other table" would be needed, to correlate each row to its ordinal column position in the format of the ASJAB1-8 table. A file that does not reuse deleted records _could_ use RRN to ensure that, given the inserted data was *always* inserted sequentially [not as a set] in the same order; i.e. col1 to col8 always inserted as the first to eighth row, but that would be difficult to ensure, and any reorg by some key break that.

It is a relatively simple recursive query to concat the rows to make a single column, but a table function or a procedure for a result set from a temporary table would probably be required if the SELECT against the ASJAB1-8 table refers to the columns by their names. Of course the first is not an option due to the release; irrespective of its inability to effect multiple columns. The second and third limit the SELECT to read-only queries even though they can establish columns. For the second, I am not sure if there would be a way to keep the statements the same, so it is probably not an option either. For the third, it is really just a matter of changing the recently given procedure to do an insert from a select cursor of the file with the data instead of the rows generated from variables; i.e. in the message:
http://archive.midrange.com/midrange-l/200808/msg00331.html

For going from columns to rows as an unpivot query, that probably would require CASE to exclude the union all of the columns that should not become rows; per the message:
http://archive.midrange.com/rpg400-l/200807/msg00180.html

Regards, Chuck

Pete Helgren wrote:

After perusing the web for an hour or so looking for a technique to handle this, I haven't found a solution so perhaps some of the SQL
gurus can lend a hand.

I have an application that has a select statement that basically
selects 9 columns of data from a single row in a table. Like this:

SELECT ASJD#, ASJAB1, ASJAB2, ASJAB3, ASJAB4, ASJAB5, ASJAB6, ASJAB7,
ASJAB8 FROM MyFile

I also have two other databases that have similar data but the table structure is different. Rather than have a single record with 8
columns of data (ASJAB1-8) it can have a recurring sequence of up to
8 rows of data. The database can vary by customer. I can't control
what database they use. For simplicity sake, let's say they have two
fields:

ID, ASJAB

This could repeat up to 8 times. So with data in the record (again, simplified) one record in one of the databases could be like this:

1234, 'stuff here', 'more stuff here', 'any stuff here', 'my stuff here', 'other stuff here', 'could be stuff here', 'no stuff here',
'end of stuff here'

but the other table in the other database might have data like this:

1234, 'stuff here'
1234, 'more stuff here'
1234, 'any stuff here'
1234, 'my stuff here'
1234, 'other stuff here'
1234, 'could be stuff here'
1234, 'no stuff here'
1234, 'end of stuff here'

My goal is not not write database specific code.

Just to add an additional wrinkle. The rows *may* recur up to 8 times
but can vary from 0 to 8 occurrences. The fixed record *always* has eight columns but the two column table with multiple occurrences in
the rows can vary from 0 to 8. So, I almost think that I need a loop
to loop through the results and "build" a record from that but I am
out of gas on my SQL skills at that point.

I *want* to handle this without changing the SQL statement in the application. I would just like a view or procedure that returns a record that "looks" the same to the application. The reason is that
it simplifies the SQL processing so I don't have to change all the
SQL statements in my application with a conditional statement that
says if the table is "X" then do this. If it is "Y", then do this. If
it is "Z" do it this way. A view or stored procedure that returned a
similar record keeps it simple but may be impossible?

I am on V5R3M0. I am still poking around the web but I can't seem to find anything similar.