# How to transpose data of a json file into a sql table

**URL:** <https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847>\
**Category:** General\
**Created:** [28 August 2019 07:48 UTC](https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847 "2019-08-28T07:48:57Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stud](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@stud](https://discourse.nodered.org/u/stud)\
**Post date:** [28 August 2019 07:48 UTC](https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847/1 "2019-08-28T07:48:57Z")

</div>

I'm working on a mysql database for measurements. The data of the measurements are saved as a csv file, which I managed to be converted into json. Now my problem is that I want only the last rows of the csv to be transposed into two additional columns 'characteristics' and 'value'.

{ "Date": "08.03.2019", "machine": "G2", "ordernr": 1234567890, "TTNR": 311436, "L\_S1": "36,92", "L\_S2": "23,9", "L\_KS1": "7,8", "L\_KS1": "6,6", }

the expected table has the column names 'date', 'machine', 'ordernr', 'characteristics' and 'value'

the characteristics should be L\_S1, L\_S2, L\_KS1, L\_KS2 and the value: 36.92, 23.9, 7.8, 6.6

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [28 August 2019 08:10 UTC](https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847/2 "2019-08-28T08:10:54Z")

</div>

When you create the INSERT statement you specify the that you want to insert data into a column and where that bit of data is in the msg object.

The order it is within there doesn’t matter

---

<div class="post-metadata">

**Author:** ![stud](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@stud](https://discourse.nodered.org/u/stud)\
**Post date:** [28 August 2019 09:18 UTC](https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847/3 "2019-08-28T09:18:48Z")

</div>

so if i have 20 different characteristics, do have to right them all in my function node down ?

insertIntoTemplate += ''' + msg.payload['L\_S1','L\_S2','L\_KS1','L\_KS2'] + ''';

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [28 August 2019 10:47 UTC](https://discourse.nodered.org/t/how-to-transpose-data-of-a-json-file-into-a-sql-table/14847/4 "2019-08-28T10:47:08Z")

</div>

If you want them in your database - yes.