Show values from different temptables in one table

Question

My sql tables are as follows:-

 CREATE TABLE #TmpA (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT
  );

CREATE TABLE #TmpB (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT );

CREATE TABLE #TmpC (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT );

 INSERT INTO #TmpA VALUES('P', 'Name1',0,278),('P', 'Name2',10,89),('R', 'Name3',5,89)

 INSERT INTO #TmpB VALUES ('P', 'Name1',0,10),('P', 'Name2',1,78),('A', 'Name4',4,289 )

 INSERT INTO #TmpC VALUES ('P', 'Name1',54,67),('P', 'Name5',5,47),('A', 'Name6',3,90 )

SELECT * FROM #TmpA
SELECT * FROM #TmpB
SELECT * FROM #TmpC

I want to combine all my tables into one table for that I have created one more table #TmpD. I want to show in following format:-

The column #TmpD needs to be dynamic in this example I have taken 3 but there can be more than 3 or less than 3 also.

Answer 1

Another method using a dynamic pivot:

---- create new table #tmpD by using select ... into
select *
into #tmpD
from  
    (      
        select *, 1 as reqNr  from #TmpA union all
        select *, 2  from #tmpB union all
        select *, 3  from #tmpc 
    ) t

declare @cols_req as nvarchar(max)
     ,  @cols_req_max as nvarchar(max)
     ,  @cols_due as nvarchar(max)
     ,  @cols_due_max as nvarchar(max)
     ,  @query  as nvarchar(max)

select @cols_req = stuff((select distinct ',' + quotename('Request'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_req_max = stuff((select distinct ',' + ('max(Request'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10)) +') as Request' + cast(row_number() over(partition by col1, type1 order by col1, type1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_due = stuff((select distinct ',' + quotename('Due'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_due_max = stuff((select distinct ',' + ('max(Due'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10)) +') as Due' + cast(row_number() over(partition by col1, type1 order by col1, type1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')

set @query = '    select Type1, Col1, isnull(Request1, 0) Request1, isnull(Due1, 0) Duel1, isnull(Request2, 0) Request2, isnull(Due2, 0) Due2, isnull(Request3, 0) Request3, isnull(Due3, 0) Due3 
                  from (
                          select Type1, Col1, ' + @cols_req_max + ', ' + @cols_due_max + '
                          from 
                                (
                                    select  Type1
                                          , Col1
                                          , Request
                                          , Due
                                          , col_req = ''Request''+ cast(reqNR as varchar(10))
                                          , col_due = ''Due''+ cast(reqNR as varchar(10))
                                    from #tmpD 
                                ) x
                          pivot ( max(request) for col_req in (' + @cols_req + ') )p  
                          pivot ( max(due) for col_due in (' + @cols_due + ') ) q
                          group by Type1, Col1
                       ) t
              '
print @query
execute sp_executesql @query;

I initially created a new temporary table (#tmpD) where I inserted rows from all the three initial tables, along with an extra column called 'reqNr' which shows which table was the source.

For the cases when you have more than 3 tables, you only need to adjust the initial statement where you insert all rows into the #tmpD table, to include the other tables as well.

You can check out a working demo here.

Answer 2

One method uses union all and aggregation:

select type1, col1,
       max(request1) as request1, max(due1) as due1,
       max(request2) as request2, max(due2) as due2,
       max(request3) as request3, max(due3) as due3
from ((select type1, col1,
              request as request1, due as due1,
              0 as request2, 0 as due2,
              0 as request3, 0 as due3
       from #tmpa
      ) union all
      (select type1, col1,
              0 as request1, 0 as due1,
              request as request2, due as due2,
              0 as request3, 0 as due3
       from #tmpb
      ) union all
      (select type1, col1,
              0 as request1, 0 as due1,
              0 as request2, 0 as due2,
              request as request3, due as due3
       from #tmpc
      )
     ) abc
group by type1, col1;

Another approach is full join, but this can be tricky:

select a.type1, a.col1,
       coalesce(a.request, 0) as request1, coalesce(a.due, 0) as due1,
       coalesce(b.request, 0) as request2, coalesce(b.due, 0) as due2,
       coalesce(c.request, 0) as request3, coalesce(c.due, 0) as due3
from #tmpA a full join
     #tmpB b
     on b.type1 = a.type1 and b.col1 = a.col1 full join
     #tmpC c
     on c.type1 = coalesce(a.type1, b.type1) and
        c.col1 = coalesce(a.col1, b.col1);

Answer 3

SELECT  T.Col1, 
        ISNULL(A.Request,0) AS Request1,
        ISNULL(A.Due,0) AS Due1,
        ISNULL(B.Request,0) AS Request2,
        ISNULL(B.Due,0) AS Due2,
        ISNULL(C.Request,0) AS Request3,
        ISNULL(C.Due,0) AS Due3
FROM 
(
    SELECT Col1
    FROM #TmpA
    UNION 
    SELECT Col1
    FROM #TmpB
    UNION
    SELECT Col1
    FROM #TmpC
)T
LEFT JOIN #TmpA A
    ON A.Col1=T.Col1
LEFT JOIN #TmpB B
    ON B.Col1=T.Col1
LEFT JOIN #TmpC C
    ON C.Col1=T.Col1