‘Unpivoting’ a SQL table

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:

Column_A Column_B Column_C Column_D
000 A;B;C;D 01;02;03;04 X;Y;D;E
001 A;B 05;06 S;T
002 C 07 S

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:

Column_A Column_B Column_C Column_D
000 A 01 X
000 B 02 Y
000 C 03 D
000 D 04 E
001 A 05 S

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!



Read more here: https://stackoverflow.com/questions/68472526/unpivoting-a-sql-table

Content Attribution

This content was originally published by fermie at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: