MARAIDB ROWSET ONE RECORD TO COVERT INTO COLUMNS

[shrifw]

Dear Rao Sir ,

As we convert SQL result set from rows to columns. Is there any way to convert MariaDB Rowset into columns. for the given below examples:

Code: Select all  Expand view

Original  RowSet Result :
 col_name     col_value
 ------------     ---------------
 name            Suresh
 age               22
 dob               01/01/2021

AFTER CONVERTING
name         age             dob
========  ========     ==========
Suresh      22                01/01/2021
 

Thanks
Shridhar

[nageswaragunupudi]

We need to start with this table.

Code: Select all  Expand view

Original  RowSet Result :
 col_name     col_value
 ------------     ---------------
 name            Suresh
 age               22
 dob               01/01/2021
 

Let is first create this table and keep it ready for actual processing.
We will use FW's cloud server which is available for all of us.

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn

   oCn   := maria_Connect( "208.91.198.197:3306","fwhdemo", "gnraofwh", "Bharat@1950" )

   oCn:Droptable( "tbl_struct" )
   oCn:CreateTable( "tbl_struct", { { "col_name", "C", 20, 0 }, { "col_value", "C", 20, 0 } }, .f. )
   oCn:Insert( "tbl_struct", nil, {{ "name","suresh"}, {"age",22},{"dob","01/01/2001"}} )

   XBROWSER oCn:tbl_struct TITLE "tbl_struct"
   oCn:Close()
   
return nil

Now let us create the final table from the data in the first table "tbl_struct"

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn
   local aData
   local aStruct := {}
   local aRowData := {}

   oCn   := maria_Connect( "208.91.198.197:3306","fwhdemo", "gnraofwh", "Bharat@1950" )

   aData    := oCn:Execute( "select * from tbl_struct" )
   AEval( aData, { |a,i| AAdd( aStruct, { a[ 1 ], "C", 20, 0 } ), ;
                         AAdd( aRowData, a[ 2 ] ) } )

   oCn:DropTable( "tbl_data" )
   oCn:CreateTable( "tbl_data", aStruct, .f. )
   oCn:Insert( "tbl_data", nil, aRowData )

   XBROWSER oCn:tbl_data TITLE "tbl_data"

   oCn:Close()

return nil

[shrifw]

Dear Rao Sir ,

Thanks a lot...!

Thanks
Shridhar