Combining 2 tables

Transact-SQL
1

Hello SQL expert,

I am trying to combine these two tables, TBL1 and TBL2 into one table called "Expecting TBL"

Here is the Create and Insert SQL statement for TBL1 and TBL2.

CREATE TABLE TBL1
(
ID CHAR(4),
CODE VARCHAR(10),
UNIT INT
)

INSERT INTO TBL1 (ID, CODE, UNIT)
VALUES ('APM1','12','1500'),('APM1','33','4500'),('APM1','33','200'),('APM1','525','150'),('APM1','525','100'),('APM1','667','175')
,('APM2','33','12500'),('APM2','563','5500'),('APM2','711','1500')
,('APM3','223','5000'),('APM3','223','4500'),('APM3','4','250')

CREATE TABLE TBL2
(
ID CHAR(4),
NAME VARCHAR(10),
COLOR VARCHAR(10)
)

INSERT INTO TBL2 (ID, NAME, COLOR)
VALUES ('APM1','John','Black'),('APM1','John','Red'),('APM1','John','Blue'),('APM1','John','Yellow'),('APM1','John','Green'),('APM1','John','Black'),('APM1','John','White'),('APM1','John','Brown'),('APM1','John','Red'),('APM1','John','Blue')
,('APM2','Mel','12500'),('APM2','Mel','Red'),('APM2','Mel','White')
,('APM3','Todd','Green'),('APM3','Todd','Black')

And here are the TBL1 and TBL2 output:

TBL1N2
TBL1N2421×954 169 KB

and here is the output I want to see in one table:

output
output1197×523 280 KB

Anyone knows how can I achieve this in SQL? FYI, the number of records in TBL1 could be more so that will expand the columns in the combining table to the right handside.

Thank you

3

There needs to be seq#s in the tables to do this accurately, so I added one to each of your tables, TBL1 and TBL2. Before loading each table, run this ALTER statement on it:
ALTER TABLE TBL1 /* and TBL2 */ ADD SEQ# int IDENTITY(1, 1) NOT NULL;
That's the only change I made to any of your sample table setup or data.

SELECT
    ID, NAME, COLOR,
    CODE01, UNIT01, CODE02, UNIT02,
    CODE03, UNIT03, CODE04, UNIT04,
    CODE05, UNIT05, CODE06, UNIT06,
    CODE07, UNIT07, CODE08, UNIT08
FROM (
    SELECT 
        T2.ID,
        T2.NAME,
        T2.COLOR,
        T2.row_num,
        MAX(CASE WHEN T1_CODE.row_num = 1 THEN T1.CODE END) AS CODE01,
        MAX(CASE WHEN T1_CODE.row_num = 1 THEN T1.UNIT END) AS UNIT01,
        MAX(CASE WHEN T1_CODE.row_num = 2 THEN T1.CODE END) AS CODE02,
        MAX(CASE WHEN T1_CODE.row_num = 2 THEN T1.UNIT END) AS UNIT02,
        MAX(CASE WHEN T1_CODE.row_num = 3 THEN T1.CODE END) AS CODE03,
        MAX(CASE WHEN T1_CODE.row_num = 3 THEN T1.UNIT END) AS UNIT03,
        MAX(CASE WHEN T1_CODE.row_num = 4 THEN T1.CODE END) AS CODE04,
        MAX(CASE WHEN T1_CODE.row_num = 4 THEN T1.UNIT END) AS UNIT04,
        MAX(CASE WHEN T1_CODE.row_num = 5 THEN T1.CODE END) AS CODE05,
        MAX(CASE WHEN T1_CODE.row_num = 5 THEN T1.UNIT END) AS UNIT05,
        MAX(CASE WHEN T1_CODE.row_num = 6 THEN T1.CODE END) AS CODE06,
        MAX(CASE WHEN T1_CODE.row_num = 6 THEN T1.UNIT END) AS UNIT06,
        MAX(CASE WHEN T1_CODE.row_num = 7 THEN T1.CODE END) AS CODE07,
        MAX(CASE WHEN T1_CODE.row_num = 7 THEN T1.UNIT END) AS UNIT07,
        MAX(CASE WHEN T1_CODE.row_num = 8 THEN T1.CODE END) AS CODE08,
        MAX(CASE WHEN T1_CODE.row_num = 8 THEN T1.UNIT END) AS UNIT08
        /* ... UNIT09, ... */
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY SEQ#) AS row_num
        FROM TBL2
    ) AS T2
    LEFT OUTER JOIN (
        SELECT ID, CODE, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY SEQ#) AS row_num
        FROM (
            SELECT ID, CODE, MIN(SEQ#) AS SEQ#
            FROM TBL1
            GROUP BY ID, CODE
        ) AS T1_CODE
    ) AS T1_CODE ON T1_CODE.ID = T2.ID
    LEFT OUTER JOIN (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ID, CODE ORDER BY SEQ#) AS row_num
        FROM TBL1
    ) AS T1 ON T1.ID = T2.ID AND T1.row_num = T2.row_num AND T1.CODE = T1_CODE.CODE
    GROUP BY T2.ID, T2.NAME, T2.COLOR, T2.SEQ#, T2.row_num
) AS derived
ORDER BY ID, row_num
2 Likes
4

ScottPletcher, you are very smart. It works!

5

Thank you! (Yes, a rather tricky query.)