So i got some rough data from an SQL, it is in JSON and it's poorly formatted. I've somehow managed to format it almost how i want it, now i'm just limited by my skills and i can't seem to find anyone with a similar issue. I've tried several things, split by list, group, transposing you name it, i just can't seem to get it right.
I've visualized some sample data because i think that explains it better. Basically i want to transpose the first columns rows into columns, and add the values. Everytime it gets to a new column 1, that means it's a new row. It's not identical, sometimes column4name will be missing, sometimes there's only 5 columnnames, other times there's duplicate columnnames that needs to be combined with a delimiter (comma and space ", "). The author and link columns are identical until the next column1, so they only need to be added once as a new column after the transposed columns.
Basically i want this data to be transformed into the table below it if possible.
So from the above table to this table below
I've visualized some sample data because i think that explains it better. Basically i want to transpose the first columns rows into columns, and add the values. Everytime it gets to a new column 1, that means it's a new row. It's not identical, sometimes column4name will be missing, sometimes there's only 5 columnnames, other times there's duplicate columnnames that needs to be combined with a delimiter (comma and space ", "). The author and link columns are identical until the next column1, so they only need to be added once as a new column after the transposed columns.
Basically i want this data to be transformed into the table below it if possible.
ColumnNames | Values | Author (same value until next column1) | Link (same value until next column1) |
Column1Name (column1 will always be start of a new row) | 1 | Author1 | http:/Link1.com |
Column2Name | 2 | Author1 | http:/Link1.com |
Column3Name | 3 | Author1 | http:/Link1.com |
Column3Name (There can be duplicate column names per row, join by comma) | 4 | Author1 | http:/Link1.com |
Column5Name | 5 | Author1 | http:/Link1.com |
Column1Name (column1 will always be start of a new row) | a | Author2 | http:/Link2.com |
Column2Name | b | Author2 | http:/Link2.com |
Column3Name | c | Author2 | http:/Link2.com |
Column4Name | d | Author2 | http:/Link2.com |
Column5Name | e | Author2 | http:/Link2.com |
Column6Name | f | Author2 | http:/Link2.com |
Column7Name | g | Author2 | http:/Link2.com |
Column7Name | h | Author2 | http:/Link2.com |
Column7Name (There can be duplicate column names per row, join by comma) | i | Author2 | http:/Link2.com |
Column1Name (column1 will always be start of a new row) | 101 | Author3 | http:/Link3.com |
Column2Name | 102 | Author3 | http:/Link3.com |
Column3Name | 103 | Author3 | http:/Link3.com |
Column3Name (There can be duplicate column names per row, join by comma) | 104 | Author3 | http:/Link3.com |
Column5Name | 105 | Author3 | http:/Link3.com |
Column6Name | 106 | Author3 | http:/Link3.com |
Column7Name | 107 | Author3 | http:/Link3.com |
Column8Name | 108 | Author3 | http:/Link3.com |
Column1Name | Column2Name | Column3Name | Column4Name | Column5Name | Column6Name | Column7Name | Column8Name | Author | Link |
1 | 2 | 3, 4 | null | 5 | null | null | null | Author1 | http:/Link1.com |
a | b | c | d | e | f | g, h, i | null | Author2 | http:/Link2.com |
101 | 102 | 103, 104 | null | 105 | 106 | 107 | 108 | Author3 | http:/Link3.com |