Adding columns across multiple tables with same column name

Question

I have 10 tables named T1, T2, T3, ...,T10 and each store columns named ID, C1, C2, C3 ...., C100.

I want to create a summary tables which stores the sum of corresponding columns i.e. T1.C1 + T2.C1 + T3.C1, T1.C2 + T2.C2 + T3.C2 and so on.

If there was only few columns I could have done something as:

select T1.C1 + T2.C1 + T3.C1 as C1, T1.C2 + T2.C2 + T3.C2 as C2
from T1
inner join T2
on T1.ID = T2.ID
inner join T3
on T1.ID = T3.ID

but I have 100 columns to add and I have large number of tables.

Please tag the appropriate RDMS ... SQL Server, Oracle, ...? — John Cappelletti
– John Cappelletti, Commented Sep 23, 2016 at 16:58

Serg · Accepted Answer · 2016-09-23 17:23:22Z

2

Assuming ID is a primary key in every table:

select ID, sum(c1) as c1, sum(c2) as c2 --,...
from(
   select ID, c1, c2 --,..
   from T1
   union all
   select ID, c1, c2 --,..
   from T2
   -- union all ..
) T
group by ID

answered Sep 23, 2016 at 17:23

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JamieD77 Over a year ago

should be able to use select * if the columns are identical

John Cappelletti · Accepted Answer · 2016-09-23 18:20:11Z

Just for fun, you may want to consider the following. The sub-query will normalize your data in an EAV (Element Attribute Value) structure

Declare @XML xml = (Select * From ( Select * from T1 Union All Select * from T2 Union All Select * from T3 Union All Select * from T4 Union All Select * from T5 Union All Select * from T6 Union All Select * from T7 Union All Select * from T8 Union All Select * from T9 Union All Select * from T10) A for XML RAW)
Select ID
      ,Item
      ,Value = Sum(Value)
 From (
        Select ID    = r.value('@ID','int')                            -- << Key ID (Case Sensitive)    
              ,Item  = attr.value('local-name(.)','varchar(50)')
              ,Value = attr.value('.','money')                         -- << Set Appropriate Data Type
         From  @XML.nodes('/row') as A(r)                         
         Cross Apply A.r.nodes('./@*[local-name(.)!="id"]') as B(attr) -- << Key ID (Case Sensitive)    
      ) A
 Group By ID,Item

John Cappelletti · Accepted Answer · 2016-09-23 18:25:58Z

If you don't mind going dynamic

Declare @Cols varchar(max) = '>>>'
;with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
      cte2(N) As (Select Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b) A )   --<< 100 Columns
Select @Cols = Replace(@Cols+',C'+cast(N as varchar(5))+'=sum(C'+cast(N as varchar(5))+')','>>>,','') from cte2

Declare @From varchar(max) = '>>>'
;with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
      cte2(N) As (Select Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a) A )   --<< 10 Tables
Select @From = Replace( @From+'Union All Select * from T'+cast(N as varchar(5))+' ','>>>Union All','') from cte2

Declare @SQL varchar(max) ='Select ID,'+@Cols+' From ('+@From+') A Group By ID'  
Exec(@SQL)

Collectives™ on Stack Overflow

Adding columns across multiple tables with same column name

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related