'Unpivoting' a SQL table

user_16496624 0
Viewed 74

I'm looking to 'unpivot' a table, though I'm not sure what the best way of going about it, is. Additionally, the values are separated by a ';'. 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.

As an update, there were really helpful solutions below. They all ran as expected, however I had one last addition. Is it possible/reasonable to ignore a row/column if it's blank? For example, skipping over Column_C where Column_A is '001'. |Column_A|Column_B|Column_C|Column_D| |:-|:-|:-|:-| |000|A;B;C;D|01;02;03;04|X;Y;D;E| |001|A;B||S;T| |002|C|07|S|

tsql sql-server sql
3 Answers

You can use a recursive CTE to walk through the strings. Assuming they are all the same length (i.e. same number of semicolons):

with cte as (
      select a, convert(varchar(max), null) as b, convert(varchar(max), null) as c, convert(varchar(max), null) as d,
             convert(varchar(max), b + ';') as rest_b, convert(varchar(max), c + ';') as rest_c, convert(varchar(max), d + ';') as rest_d,
             0 as lev
      from t
      union all
      select a,
             left(rest_b, charindex(';', rest_b) - 1),
             left(rest_c, charindex(';', rest_c) - 1),
             left(rest_d, charindex(';', rest_d) - 1),
             stuff(rest_b, 1, charindex(';', rest_b), ''),
             stuff(rest_c, 1, charindex(';', rest_c), ''),
             stuff(rest_d, 1, charindex(';', rest_d), ''),
             lev + 1
      from cte
      where rest_b <> ''
     )
select a, b, c, d
from cte
where lev > 0
order by a, lev;

Here is a db<>fiddle.

EDIT:

You can ensure filter to use rows that have the same number of semicolons by using:

where (length(b) - length(replace(b, ';', ''))) = (length(c) - length(replace(c, ';', ''))) and
      (length(b) - length(replace(b, ';', ''))) = (length(d) - length(replace(d, ';', '')))

You could also extend c and d with a bunch of semicolons so no error occurs and the resulting values are empty strings. Extra semicolons in those columns don't matter, so you could use:

      select a, convert(varchar(max), null) as b, convert(varchar(max), null) as c, convert(varchar(max), null) as d,
             convert(varchar(max), b + ';') as rest_b, convert(varchar(max), c + replicate(';', length(b))) as rest_c, convert(varchar(max), d + replicate(';', length(b))) as rest_d,
             0 as lev
user_11440350

Here is a JSON based method. SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ColA varchar(3), ColB varchar(8000), ColC varchar(8000), ColD varchar(8000));
INSERT INTO @tbl VALUES
('000','A;B;C;D','01;02;03;04','X;Y;D;E'),
('001','A;B','05;06','S;T'),
('002','C','07','S');
-- DDL and sample data population, end

WITH rs AS 
(
     SELECT *
         , ar1 = '["' + REPLACE(ColB, ';', '","') + '"]'
         , ar2 = '["' + REPLACE(ColC, ';', '","') + '"]'
         , ar3 = '["' + REPLACE(ColD, ';', '","') + '"]'
     FROM @tbl
 )
 SELECT ColA, ColB.[value] AS [ColB], ColC.[value] AS ColC, ColD.[value] AS ColD
 FROM rs
    CROSS APPLY OPENJSON (ar1, N'$') AS ColB
    CROSS APPLY OPENJSON (ar2, N'$') AS ColC
    CROSS APPLY OPENJSON (ar3, N'$') AS ColD
 WHERE ColB.[key] = ColC.[key]
    AND ColB.[key] = ColD.[key];

Output

+------+------+------+------+
| ColA | ColB | ColC | ColD |
+------+------+------+------+
|  000 | A    |   01 | X    |
|  000 | B    |   02 | Y    |
|  000 | C    |   03 | D    |
|  000 | D    |   04 | E    |
|  001 | A    |   05 | S    |
|  001 | B    |   06 | T    |
|  002 | C    |   07 | S    |
+------+------+------+------+
user_19323110

The real problem here is your design. Hopefully the reason you're doing this is to fix your design.

Unfortunately you can't use SQL Server's inbuilt STRING_SPLIT for this, as it doesn't provide an ordinal position. As such I use DelimitedSplit8K_LEAD to separate the values into rows, and then "join" then back up. This does assume that all the columns have the same number of delimited values.

CREATE TABLE dbo.YourTable (ColA varchar(3),
                            ColB varchar(8000),
                            ColC varchar(8000),
                            ColD varchar(8000));
INSERT INTO dbo.YourTable
VALUES('000','A;B;C;D','01;02;03;04','X;Y;D;E'),
      ('001','A;B','05;06','S;T'),
      ('002','C','07','S');
GO

SELECT YT.ColA,
       DSLB.Item AS ColB,
       DSLC.Item AS ColC,
       DSLD.Item AS ColD
FROM dbo.YourTable YT
     CROSS APPLY dbo.DelimitedSplit8K_LEAD(YT.ColB,';') DSLB
     CROSS APPLY dbo.DelimitedSplit8K_LEAD(YT.ColC,';') DSLC
     CROSS APPLY dbo.DelimitedSplit8K_LEAD(YT.ColD,';') DSLD
WHERE DSLB.ItemNumber = DSLC.ItemNumber
  AND DSLC.ItemNumber = DSLD.ItemNumber;
GO
DROP TABLE dbo.YourTable;
user_20299830
Related