Rows linked each other INTO columns

Question

My challenging challenge for today is: how to achieve Desired Output Table from Input Table:

DECLARE @table1 TABLE
(
    [Sub1] CHAR(1),
    [Sub2] CHAR(1)
);

INSERT INTO @table1
VALUES
('A', 'B'),
('A', 'D'),
('A', 'F'),
('A', 'E'),
('A', 'A'),
('A', 'C'),
('A', 'G'),
('C', 'A'),
('B', 'A'),
('X', 'Z');

select * from @table1;

The idea is: all elements (from A to G) are substitutes:

B replaces A, E replaces A so B replaces not only A but E as well and so on. However X and Z substitute only each other - none of them has connection to any others of elements.

Finally I would like to have all parts listed and their possible substitutes.

I would greatly appreciate any suggestion on that.

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — JeffUK, Feb 01 '19 at 13:48
Hi JeffUK, unfortunately I do not know how to solve my case with post linked. — arhetyp, Feb 01 '19 at 18:33

LukStorms · Answer 1 · 2019-02-05T06:58:06.190

0

Take a double forward,

Then tripple it backwards,

And pivot all around!

(on the tunes of "Sing, sing, sing")

declare @DoubleSub table (
    [Sub1] CHAR(1) NOT NULL,
    [Sub2] CHAR(1) NOT NULL,
    PRIMARY KEY ([Sub1], [Sub2])
);

INSERT INTO @DoubleSub ([Sub1], [Sub2])
SELECT [Sub1], [Sub2] FROM @table1
UNION
SELECT [Sub2], [Sub1] FROM @table1;

;WITH TRIPLESUB AS
(
    SELECT [Sub1] as [Main], 1 as Lvl, [Sub1], [Sub2], CAST('|'+ [Sub1] +'|'+ [Sub2] +'|' AS VARCHAR(200)) as Visited
    FROM @DoubleSub

    UNION ALL

    SELECT c.[Main], c.Lvl + 1, t.[Sub1], t.[Sub2],
    CAST(c.Visited + t.[Sub2] + '|' AS VARCHAR(200))
    FROM TRIPLESUB c
    JOIN @DoubleSub t
      ON t.[Sub1] = c.[Sub2] 
     AND c.Lvl < 12
     AND c.[Main] != t.[Sub2]
     AND c.Visited NOT LIKE CONCAT('%|',t.[Sub2],'|%')
)
SELECT 
[Main], 
[1] as [Substitute1],
[2] as [Substitute2],
[3] as [Substitute3],
[4] as [Substitute4],
[5] as [Substitute5],
[6] as [Substitute6]
FROM 
(
  SELECT [Main], [Sub2],
   ROW_NUMBER() OVER (PARTITION BY [Main] ORDER BY IIF([Main]<[Sub2],0,1), [Sub2]) AS RN
  FROM TRIPLESUB
  WHERE [Main] != [Sub2]
  GROUP BY [Main], [Sub2]
) AS src
PIVOT
(
  MAX([Sub2])
  FOR RN IN ([1],[2],[3],[4],[5],[6])
) AS pvt;

A test here

Returns:

Main        Substitute1 Substitute2 Substitute3 Substitute4 Substitute5 Substitute6
A           B           C           D           E           F           G
B           C           D           E           F           G           A
C           D           E           F           G           A           B
D           E           F           G           A           B           C
E           F           G           A           B           C           D
F           G           A           B           C           D           E
G           A           B           C           D           E           F
X           Z           NULL        NULL        NULL        NULL        NULL
Z           X           NULL        NULL        NULL        NULL        NULL

edited Feb 05 '19 at 06:58

answered Feb 02 '19 at 20:22

LukStorms

28,916
5
31
45

Than you LukStorms , seems to work according to expectations. Only one question - is it possible to ensure that main Main part is not duplicated in any of Subsitutes columns? For sample date it is abolutely fine but for real data sometimes original pair is AA or XX and for those case I would like not to return the same element as substitute. – arhetyp Feb 03 '19 at 18:22
I think you can simply add a `WHERE [Sub1] != [Sub2]` is the `src` sub-query. – LukStorms Feb 03 '19 at 19:25
@ArkadiuszCiesielski I've updated the answer. It now uses a table variable and a recursive CTE that limits for the circular loops. I think a temporary table is better for the initial doubling of the substitutes, since a PK can help with the joins. And there's that thing in MS Sql Server where the query optimizer can treat a CTE like it's a template. – LukStorms Feb 03 '19 at 20:04
LukStorms looks fine but the case is it returns now about 10% rows fewer than in original query:) i have to investigate it. – arhetyp Feb 03 '19 at 20:49
@arhetyp I made the recursive CTE a bit more resistant against circular loops now. That should return a higher % from your data. – LukStorms Feb 03 '19 at 21:44
LukStorms, thanks for your efforts but still some rows are missing:)) – arhetyp Feb 04 '19 at 20:48
No idea then. Without having your data, nor an idea which are missing, it's impossible to figure out why. Can you simulate it with sample data? – LukStorms Feb 04 '19 at 21:09
@arhetyp Is it perhaps because you have some with more than 6 substitutes? Because this SQL has a limit on c.Lvl & the pivot is hardcoded for 6. There is a way to use Dynamic Sql for the pivot to make the number of Substitute columns based on the max RN. – LukStorms Feb 05 '19 at 08:25

Rows linked each other INTO columns

1 Answers1