"Unpivot" table columns to rows

[Mitch]

Hi all,

Have a need to convert fields to rows leaving the original table intact and storing the result in a new table (or view).  In some tools this would be referred to as an "unpivot" function.  How would I do this is Caspio?  See "Current data" and "Unpivoted data" below.  Thanks.

Current data:

	Jan	Feb
Account 1	20	40
Account 2	60	10

 

Unpivoted data:

Account 1	Jan	20
Account 2	Jan	60
Account 1	Feb	40
Account 2	Feb	10

[Nuke354]

Hi Mitch, perhaps you can use a Trigger/Task to move the un-pivoted data version of the table to another. You can then create a loop to insert records for each column. As of now, the "un-pivot" feature is not available.

 

[Mitch]

17 hours ago, Nuke354 said:

Hi Mitch, perhaps you can use a Trigger/Task to move the un-pivoted data version of the table to another. You can then create a loop to insert records for each column. As of now, the "un-pivot" feature is not available.

 

Thanks!  As  a Caspio newbie would I never have come up with that.  When I had this DB in Access I would just use a series of Union queries such as:

SELECT Jan from Table1 UNION SELECT Feb from Table1 etc...

Confirming that is not possible is Caspio?  For that matter it appears that any use of SQL isn't available ?