Merging tables into New table while retaining information

Question

I apologize in advance as the title is not very descriptive.

I have approximately 40+ tables each with the same exact table layout columns, and data save for one column. I would like to take that column which is not the same across all the tables and merge them into one table. Confusing? Let me illustrate..

SRCTbl01:

ID  TYPE  COLR1  INSTOCK
-----------------------
1    B     RED    YES
2    B     BLUE   YES
3    P     GREEN  NO
4    B     BLACK  YES

SRCTbl02:

ID  TYPE  COLR1  INSTOCK
-----------------------
1    B     RED    YES
2    B     BLUE   NO
3    P     GREEN  YES
4    B     BLACK  YES

SRCTbl03:

ID  TYPE  COLR1  INSTOCK
-----------------------
1    B     RED    YES
2    B     BLUE   NO
3    P     GREEN  NO
4    B     BLACK  NO

RESULT: (Type P to be excluded)

ID  TYPE  COLR1  SRCTbl01  SRCTbl02  SRCTbl03
----------------------------------------------
1    B     RED    YES       YES       YES
2    B     BLUE   YES       NO        NO
4    B     BLACK  YES       YES       NO

And finally after all that I'd like to make the table look something like this:

INSTOCK Table:

Customer  RED  BLUE  BLACK
---------------------------
SRCTbl1   YES  YES   YES
SRCTbl2   YES  NO    YES
SRCTbl3   YES  NO    NO

I'm not entirely sure whether I can directly manipulate the tables into looking like the final iteration so I thought I should ask how to get it to the first Result as to me it seems simpler.

Thank you for the help, I've spent a full 8 hours on this and have yet to find a way to achieve it so I have come here to ask the experts.

EDIT: To clarify, I have had zero success in achieving the results I illustrated above. I have tried using SELECT .. Union SELECT, and FULL JOINS.

Using this code resulted in duplicates ( I was trying to just get the INSTOCK)

SELECT 01.INSTOCK, 02.INSTOCK, 03.INSTOCK
FROM dbo.SRCTbl01 AS 01, dbo.SRCTbl02 AS 02, dbo.SRCTbl03 AS 03
WHERE 01.TYPE='B'

I tried very many but this is probably the closest I got.

Answer 1

you could do something like that, all in one :

You first flatten with union, then take the max 'instock' for each color by customer (MAX will work as 'YES' > 'NO')

select Customer, MAX(RED) as RED, MAX(BLUE) as BLUE, MAX(BLACK) as BLACK
FROM(
  SELECT 'SRCTbl01' as Customer, 
       CASE WHEN COLR1 = 'RED' then INSTOCK ELSE 'NO' END as RED,
       CASE WHEN COLR1 = 'BLUE' then INSTOCK ELSE 'NO' END as BLUE,
       CASE WHEN COLR1 = 'BLACK' then INSTOCK ELSE 'NO' END as BLACK
  FROM SRCTbl01
  WHERE  Type <> 'P'
UNION
  SELECT 'SRCTbl02' as Customer, 
       CASE WHEN COLR1 = 'RED' then INSTOCK ELSE 'NO' END as RED,
       CASE WHEN COLR1 = 'BLUE' then INSTOCK ELSE 'NO' END as BLUE,
       CASE WHEN COLR1 = 'BLACK' then INSTOCK ELSE 'NO' END as BLACK
  FROM SRCTbl02
  WHERE  Type <> 'P'
UNION
  SELECT 'SRCTbl03' as Customer, 
       CASE WHEN COLR1 = 'RED' then INSTOCK ELSE 'NO' END as RED,
       CASE WHEN COLR1 = 'BLUE' then INSTOCK ELSE 'NO' END as BLUE,
       CASE WHEN COLR1 = 'BLACK' then INSTOCK ELSE 'NO' END as BLACK
  FROM SRCTbl03
  WHERE  Type <> 'P'
) as a
GROUP BY Customer

(You just need to add a CREATE TABLE INSTOCK as <codegiven>)

see SqlFiddle

Answer 2

Using UNION and PIVOT, something like this should do what you want (PIVOT should work on SQL Server 2005 and newer):

SELECT Customer, [Red], [Blue], [Black]
FROM (
    SELECT 'SRCTbl01' AS Customer, COLR1, INSTOCK FROM SRCTbl01 WHERE Type <> 'P'
    UNION
    SELECT 'SRCTbl02' AS Customer, COLR1, INSTOCK FROM SRCTbl02 WHERE Type <> 'P'
    UNION
    SELECT 'SRCTbl03' AS Customer, COLR1, INSTOCK FROM SRCTbl03 WHERE Type <> 'P'
    /* UNION ... */
) AS SRC
PIVOT (
    MAX(INSTOCK)
    FOR COLR1 IN ([Red], [Blue], [Black])
) AS pvt