SQL Server – Building a Bridge Table Using SELECT INTO

many-to-manyselectsql serverssist-sql

I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data.

Problem (in my case N is actually 22, id_column reference same table)

|id | id_column1 | id_column2 | id_columnN |  stuff |
+---+------------+------------+------------+--------|    
| 1 |     2017   |    2024    |    1452    |   red  |
| 2 |     4125   |    NULL    |    5234    |   blue |
| 3 |     NULL   |    5234    |    7234    |   pink |

I want to build only one bridge table from the above, but I don't want to extract all columns in that manner, my goal is to have someting like:

|idT1| idT2 |
+----+------+    
| 1  | 2017 |
| 1  | 2024 |
| 1  | 1452 |
| 2  | 4125 |
| 2  | NULL |
| 2  | 5234 |

and so on. I want to build such bridge table only from the data that I have in the problem above, and then reference idT1 and idT2 with adequate tables.

My ideas of:
– using pivot table did fail since I need to map all of the id values.
– i was thinking about using some cursor to map all of the result into one variable bounded with the id value, but also it did not work for me.

Thanks in advance for your time and help!

Best Answer

Instead of use a CURSOR I'd suggest you to use an iterative solution using WHILE.

CREATE TABLE MEASURES (id int, id_column1 int, id_column2 int, id_column3 int, stuff varchar(20));
INSERT INTO MEASURES VALUES
(1, 2017, 2024, 1452, 'red'),
(2, 4125, NULL, 5234, 'blue'),
(3, NULL, 5234, 7234, 'pink');
GO

CREATE TABLE BRIDGE (id int, idc int);
GO

DECLARE @idColumn INT = 1;
DECLARE @cmd VARCHAR(MAX) = '';

WHILE @idColumn <= 3
BEGIN
    SET @CMD =   'INSERT INTO BRIDGE'
               + ' SELECT id, id_column' 
               + CAST(@idColumn AS VARCHAR(10))
               + ' FROM MEASURES'
               -- uncomment to remove NULL values
               --+ ' WHERE id_column'
               --+ + CAST(@idColumn AS VARCHAR(10))
               --+ ' IS NOT NULL;';

    EXEC (@CMD);

    SET @idColumn = @idColumn + 1;

    -- uncomment to check SQL statements 
    --SELECT @CMD;
END

SELECT * FROM BRIDGE ORDER BY id;
GO

id |  idc
-: | ---:
 1 | 2017
 1 | 2024
 1 | 1452
 2 | 5234
 2 | null
 2 | 4125
 3 | null
 3 | 5234
 3 | 7234

dbfiddle here

Related Question