Temp Table from UNPIVOT Table

Queries and Resources
1

I have created a pivot table that transforms my data. Very basic UNPIVOT SQL Code:

SELECT ProjectID, Element, GBP FROM
( SELECT ProjectID, FacilitatingWorks,
Toxichazardouscontaminatedmaterialremoval,
Majordemolitionworks,
Specialistgroundworks,
Temporarydiversionworks,
Extraordinarysiteinvestigationworks,
Substructure,
Superstructure,
Frame,
Upperfloors,
Roof,
Stairsandramps,
Externalwalls,
Windowsandexternaldoors,
Internalwallsandpartitions,
Internaldoors,
Internalfinishes,
Wallfinishes,
Floorfinishes,
Ceilingfinishes,
Fittingsfurnishingsandequipment,
Services,
Sanitaryinstallations,
Servicesequipment,
Disposalinstallations,
Waterinstallations,
Heatsource,
Spaceheatingandairconditioning,
Ventilation,
Electricalinstallations,
Fuelinstallations,
Liftandconveyorinstallations,
Fireandlighteningprotection,
Communicationsecurityandcontrolsystems,
Specialistinstallations,
Buildersworkinconnectionwithservices,
Prefabricatedbuildingsandbuildingunits,
Worktoexistingbuildings,
Minordemolitionworksandalterationworks,
Repairstoexistingservices,
Dampproofcoursesfungusandbeetleeradication,
Facaderetention,
Cleaningexistingsurfaces,
Renovationworks,
Externalworks,
Sitepreparationworks,
Roadspathspavingsandsurfaces,
Softlandscapingplantingandirrigationsystems,
Fencingrailingsandwalls,
Externalfixtures,
Externaldrainage,
Externalservices,
Minorbuildingworksandancillarybuildings,
Maincontractorspreliminaries,
Maincontractorsoverheadsandprofit,
Projectdesignteamfees,
Otherdevelopmentprojectcosts,
RiskAllowance,
InflationAllowance,
CostTotal
FROM dbo.Cost) BasicBenchmark
UNPIVOT
(GBP FOR Element IN (FacilitatingWorks,
Toxichazardouscontaminatedmaterialremoval,
Majordemolitionworks,
Specialistgroundworks,
Temporarydiversionworks,
Extraordinarysiteinvestigationworks,
Substructure,
Superstructure,
Frame,
Upperfloors,
Roof,
Stairsandramps,
Externalwalls,
Windowsandexternaldoors,
Internalwallsandpartitions,
Internaldoors,
Internalfinishes,
Wallfinishes,
Floorfinishes,
Ceilingfinishes,
Fittingsfurnishingsandequipment,
Services,
Sanitaryinstallations,
Servicesequipment,
Disposalinstallations,
Waterinstallations,
Heatsource,
Spaceheatingandairconditioning,
Ventilation,
Electricalinstallations,
Fuelinstallations,
Liftandconveyorinstallations,
Fireandlighteningprotection,
Communicationsecurityandcontrolsystems,
Specialistinstallations,
Buildersworkinconnectionwithservices,
Prefabricatedbuildingsandbuildingunits,
Worktoexistingbuildings,
Minordemolitionworksandalterationworks,
Repairstoexistingservices,
Dampproofcoursesfungusandbeetleeradication,
Facaderetention,
Cleaningexistingsurfaces,
Renovationworks,
Externalworks,
Sitepreparationworks,
Roadspathspavingsandsurfaces,
Softlandscapingplantingandirrigationsystems,
Fencingrailingsandwalls,
Externalfixtures,
Externaldrainage,
Externalservices,
Minorbuildingworksandancillarybuildings,
Maincontractorspreliminaries,
Maincontractorsoverheadsandprofit,
Projectdesignteamfees,
Otherdevelopmentprojectcosts,
RiskAllowance,
InflationAllowance,
CostTotal)) AS UNPIVT_VALUES
WHERE ProjectID = {{table1.selectedRow.data.ProjectID}}

This UNPIVOT table creates the following table:

image
image737×444 19.1 KB

I now want to run adjustments to this data without editing the data in the databse. So I created a TempTable with this code:

image
image752×60 2.39 KB

This however, renders the following table:

image
image1505×318 10.5 KB

Clearly not what I need. The table just has to be identical to the table originally created with the UNPIVOT query but a Temp Table and not one that, if I edit, will change the data in the database.

Any help welcome

2

Hi @thomasatkinson :wave:

SQL queries tend to return as an object of arrays as opposed to an array of objects and it looks to me as though you're handling the data as the latter. We have a couple of handy functions to convert between the two.

In case you're not already, you might also want to pass the results of your query as a default value for the temp state or initialize the temp state with the SQL data using a separate event handler (to avoid having it reset every time you run your SQL query). From there, you can use the state's .setIn() method to update its data as well.

Let me know if any of this helps at all!