sql - Merging tables into New table while retaining information -

i apologize in advance title not descriptive.

i have approximately 40+ tables each same exact table layout columns, , data save 1 column. take column not same across tables , merge them 1 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 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 after i'd make table 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 can directly manipulate tables looking final iteration thought should ask how first result me seems simpler.

thank help, i've spent full 8 hours on , have yet find way achieve have come here ask experts.

edit: clarify, have had 0 success in achieving results illustrated above. have tried using select .. union select, , full joins.

using code resulted in duplicates ( trying instock)

select 01.instock, 02.instock, 03.instock dbo.srctbl01 01, dbo.srctbl02 02, dbo.srctbl03 03 01.type='b' 

i tried many closest got.

you that, in 1 :

you first flatten union, take max 'instock' each color customer (max work 'yes' > 'no')

select customer, max(red) red, max(blue) blue, max(black) black from(   select 'srctbl01' customer,         case when colr1 = 'red' instock else 'no' end red,        case when colr1 = 'blue' instock else 'no' end blue,        case when colr1 = 'black' instock else 'no' end black   srctbl01    type <> 'p' union   select 'srctbl02' customer,         case when colr1 = 'red' instock else 'no' end red,        case when colr1 = 'blue' instock else 'no' end blue,        case when colr1 = 'black' instock else 'no' end black   srctbl02    type <> 'p' union   select 'srctbl03' customer,         case when colr1 = 'red' instock else 'no' end red,        case when colr1 = 'blue' instock else 'no' end blue,        case when colr1 = 'black' instock else 'no' end black   srctbl03    type <> 'p' ) group customer 

(you need add create table instock <codegiven>)

see sqlfiddle