I have a requirement where I have a table like this -
Actual Table with 2 columns
Column1 Column2
ColAValue $$ ColBValue $$ New Row
ColCValue Above Row
ColCValue2 Above Row
$$ ColDValue Above Row
ColAValue $$ ColBValue $$ ColCValue $$ ColDValue New Row
ColAValue $$ ColBValue $$ ColCValue New Row
$$ ColDValue Above Row
I know by requirement, I would have 4 columns in my dataset leaving column 2.
I need my transformed table as a new table using query editor.
This is my expected output,
OutTable with 4 columns
Basically the column values are identified in order by delimiter $$ and if column2 says new row, then it is a new record else, it has to go and append on the current row as a new column value.
How can I transform my Input table to this output table in the query editor?
The final output Data type doesn't matter.
The initial step is to bring the row values from Above row into the New row with a delimiter and have it as a single row.