IBM Mainframe Forum

Hi,

Can some one help to convert rows into columns using recursive query concept in DB2?

Suppose I have DB2 table India_City with data as below:



By using recursive query, I have to bring output as below:


Thanks,
Sandip

CODE' d

DB2 does not have a recursive query concept! Period. Full stop. End of story.

@prino,
DB2 does have a recursive SQL concept!

@pawarsandip21
I don't think this is how recursive query works in DB2. You cannot convert a table row to a table column.
But, if you meant 'columns' as 'fields' in a file - then you can do it by using a COBOL table (like an array).

@prino - DB2 have a recursive query concept... we can use CTE (Common Table Expression) that starts with WITH command.

@pranav283 - I have to implement this in DB2....so I have to design a query for this.

Thanks,
Sandip

I have never done it..but it depends on whether you want to store all this new data into a new table.
If yes, then I guess this is what you will have to do..
1. Select the rows from original table into the host variables of the cursor. I suppose you will have to select these rows into a 2d array (one to hold the new column and one to hold the new rows for that column)
2. Then, if you have to store these into a new table keeping in mind the original storing sequence, then start reading that COBOL table one by one for each state (also holding their values) and INSERT them into the new table.

But again, this may not be sole solution. Since, the requirement is a little bit complex, you may have to redesign your pseudo code repeatedly and gradually depending on whether or not it is giving the required solution.

@pawarsandip21 - I also have same kind of query. So just wanted to know if you got the solution for the same? If you got could you please share the same here?

Thanks in advance.

Some trivial search-engining reveals examples using CTE. It shouldn't be difficult to work from those. To note, it seems that OLAP and XML processing can also achieve the same thing in DB/2 for z/OS. Is it a competition? Are you limited to using CTE?

Hi Billy,

Yes in my case I am limited to CTE. This is kind of new to me (using CTE write recursive query)

Thanks

I have seen this kind of transfomation somewhere - probably in the SQLite mailing list. The archive for this is a held in a Nabble forum.

I'd suggest getting a copy of SQLite downloaded (free) onto your home PC and use it for learning. Join the mailing list or use the archive. When searching the archive for CTE related queries search for posts after 2015 as CTEs were introduced some time in 2014.

http://ibmmainframes.com/about51395.html