I'm looking to 'unpivot' a table, though I'm not sure what the best way of going about it, is. I've listed a sample of what I'm looking at:
From that, I'm looking for a way to unpivot it, but also to keep the relations it's currently in. As in, the first value in Column_B, C, and D are tied together:
And so on.
My initial thought is to use a CTE, which I've set up as:
WITH TEST AS( SELECT DISTINCT Column\_A, Column\_B, Column\_C, VALUE AS Column\_D from \[TABLE\] CROSS APPLY STRING\_SPLIT(Column\_D, ';')) SELECT \* FROM TEST ​
Though that doesn't seem to produce the correct results, especially after stacking the CTEs and string splits.
Any help would be appreciated!