MARAIDB ROWSET ONE RECORD TO COVERT INTO COLUMNS

MARAIDB ROWSET ONE RECORD TO COVERT INTO COLUMNS

Postby shrifw » Wed May 24, 2023 10:40 am

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
shrifw
 
Posts: 69
Joined: Fri Aug 28, 2009 5:25 am

Re: MARAIDB ROWSET ONE RECORD TO COVERT INTO COLUMNS

Postby nageswaragunupudi » Wed May 24, 2023 3:08 pm

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


Image

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


Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: MARAIDB ROWSET ONE RECORD TO COVERT INTO COLUMNS

Postby shrifw » Tue May 30, 2023 3:28 am

Dear Rao Sir ,

Thanks a lot...!

Thanks
Shridhar
shrifw
 
Posts: 69
Joined: Fri Aug 28, 2009 5:25 am


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 46 guests