Create a table with unique values from another table

Question

I am using MS SQL Server Management Studio. I have table -

+--------+----------+
| Num_ID | Alpha_ID |
+--------+----------+
|   1    |    A     |
|   1    |    B     |
|   1    |    C     |
|   2    |    B     |
|   2    |    C     |
|   3    |    A     |
|   4    |    C     |
|   5    |    A     |
|   5    |    B     |
+--------+----------+

I want to create another table with 2 columns from this table so that column_1 gives Unique values in Num_ID (i.e. 1,2,3,4 and so on) and column_2 gives Unique values in Alpha_ID (A, B, C and so on).

But if an alphabet has already occurred, it should not occur again. So the output will be something like this -

Col_1  Col_2
================
1     -    A
----------------
2     -    B
----------------
3      -   NULL (as A has been chosen by 1, it cannot occur next to 3)
----------------
4    -     C
----------------
5     -    NULL (both 5 A and 5 B cannot be chosen as A and B were picked up by 1 and 2) 
----------------

Hope that makes sense. I would like to clarify that the IDs in the input table are not numerical as I have shown, but both Num_ID and Alpha_ID are complex strings. I have simplified them to 1,2,3,... and A, B, C .... for the purpose of this question

Answer 1

I don't think this could be done without a cursor. I added few more rows to your sample data to test how it works with other cases.

The logic is straight-forward. At first get a list of all distinct values of Num_ID. Then loop through them and with each iteration add one row to the destination table. To determine the Alpha_ID value to add I'll use EXCEPT operator that takes all available Alpha_ID values for the current Num_ID from the source table and removes from them all values that have been used before.

It is possible to write that INSERT without using explicit variable @CurrAlphaID, but it looks a bit cleaner with variable.

Here is SQL Fiddle.

DECLARE @TSrc TABLE (Num_ID varchar(10), Alpha_ID varchar(10));
INSERT INTO @TSrc (Num_ID, Alpha_ID) VALUES
('1', 'A'),
('1', 'B'),
('1', 'C'),
('2', 'B'),
('2', 'C'),
('3', 'A'),
('3', 'C'),
('4', 'A'),
('4', 'C'),
('5', 'A'),
('5', 'B'),
('5', 'C'),
('6', 'D'),
('6', 'E');

DECLARE @TDst TABLE (Num_ID varchar(10), Alpha_ID varchar(10));

DECLARE @CurrNumID varchar(10);
DECLARE @CurrAlphaID varchar(10);

DECLARE @iFS int;
DECLARE @VarCursor CURSOR;
SET @VarCursor = CURSOR FAST_FORWARD
FOR
    SELECT DISTINCT Num_ID
    FROM @TSrc
    ORDER BY Num_ID;

OPEN @VarCursor;

FETCH NEXT FROM @VarCursor INTO @CurrNumID;
SET @iFS = @@FETCH_STATUS;
WHILE @iFS = 0
BEGIN

    SET @CurrAlphaID = 
    (
        SELECT TOP(1) Diff.Alpha_ID
        FROM
            (
                SELECT Src.Alpha_ID
                FROM @TSrc AS Src
                WHERE Src.Num_ID = @CurrNumID

                EXCEPT

                SELECT Dst.Alpha_ID
                FROM @TDst AS Dst
            ) AS Diff
        ORDER BY Diff.Alpha_ID
    );

    INSERT INTO @TDst (Num_ID, Alpha_ID) 
    VALUES (@CurrNumID, @CurrAlphaID);

    FETCH NEXT FROM @VarCursor INTO @CurrNumID;
    SET @iFS = @@FETCH_STATUS;
END;

CLOSE @VarCursor;
DEALLOCATE @VarCursor;

SELECT * FROM @TDst;

Result

Num_ID    Alpha_ID
1         A
2         B
3         C
4         NULL
5         NULL
6         D

Having index on (Num_ID, Alpha_ID) on the source table would help. Having index on (Alpha_ID) on the destination table would help as well.