0

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!

2

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

2
  • Works perfectly, had to rename some part of the columns to work on one query, got perferct bridge for my warehouse, thank you very much, very clever approach.
    – Lake
    Jan 14 '18 at 19:44
  • I'm glad to be able to help.
    – McNets
    Jan 14 '18 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.