How do you align merged data in SQl

Question

I have three tables I want to combine into the Final Table in MSSQl2008. My problem lies in merging the data in the Final Table format. Another issue is the number of columns I have in each table close to 100 each so I need an efficient code that creates the final table

I need an SQl script that will generate the Final table

<h1> Table 2008</h1>
<table border = 1>
 <tr>
   <td> ID </Td>
   <td> Sample_year </Td>
   <td> Total_at_t-5 </Td>
   <td> Total_at_t-4 </Td>
 </tr>
<tr>
   <td> MMM  </Td>
   <td> 2008  </Td>
   <td>  5.25 </Td>
   <td>  65.25</Td>
 </tr>
  </table>

<p> </p>

<h1> Table 2011</h1>
<table header = "2011"  border = 1>
 <tr>
   <td> ID </Td>
   <td> Sample_year </Td>
   <td> Total_at_t-6 </Td>
   <td> Total_at_t-5 </Td>
 </tr>
<tr>
   <td> MMM  </Td>
   <td> 2011  </Td>
   <td>  7.25 </Td>
   <td>  98.25</Td>
 </tr>
  </table>

<p> </p>

<h1> Table 2013</h1>
<table header = "2013" border = 1>
 <tr>
   <td> ID </Td>
   <td> Sample_year </Td>
   <td> Total_at_t-7 </Td>
   <td> Total_at_t-6 </Td>
 </tr>
<tr>
   <td> MMM  </Td>
   <td> 2013  </Td>
   <td> 3.25  </Td>
   <td> 7.25</Td>
 </tr>
  </table>

<p> </p>
<h1> Table Final</h1>
<table hearder = "Final Table"  border = 1>
 <tr>
   <td> ID </Td>
   <td> Sample_year </Td>
   <td> Total_at_t-7 </Td>
   <td> Total_at_t-6 </Td>
   <td> Total_at_t-5 </Td>
   <td> Total_at_t-4 </Td>
 </tr>
<tr>
   <td> MMM </Td>
   <td> NULL </Td>
   <td> NULL </Td>
   <td> NULL </Td>
   <td> 5.25 </Td>
   <td> 65025 </Td>
 </tr>
<tr>
   <td> MMM </Td>
   <td> 2011 </Td>
   <td> NULL </Td>
   <td> 7.25</Td>
   <td> 98.25 </Td>
   <td> NULL </Td>
 </tr>
<tr>
   <td> MMM </Td>
   <td> 2013 </Td>
   <td> 3.25 </Td>
   <td> 7.25 </Td>
   <td> NUll </Td>
   <td> NUll</Td>
 </tr>
  </table>

Nice, that you have three tables, but we haven't... Please share their definition with sample data fitting to the given output. Is this output plain table data or is this somehow the result of a complex query? I'm not sure, if SQL Server is the best tool to solve this... — Shnugo, Sep 12 '16 at 22:46
@Shnugo I think your right Sql server might not be the right tool for this ...I have resorted to using SPSS to merge the data and it somewhat does what I want it to do...Reason being I have over 200 variables/columns in each table a simple sql script will take forever — Rudolf Munya Kandiwa, Sep 14 '16 at 09:17
I shortly answered a question here: http://stackoverflow.com/a/39440056/5089204. This might get you close to your goal. It allows to send **any SELECT** specified with `FOR XML` into a function and you get the whole content as `XHTML` formatted table... — Shnugo, Sep 14 '16 at 09:21
Hi, if you visited the given link I just want to inform you, that I just made an update there to make the call simpler... — Shnugo, Sep 14 '16 at 09:32

score 0 · Answer 1 · answered Sep 13 '16 at 01:48

Just assume those Table exists in SQL database, if that the case, you only need to use Union (http://www.w3schools.com/sql/sql_union.asp) to join them together

declare @T2008 table (id varchar(3), sample_year int, [total_at_t-5] decimal(10, 2), [total_at_t-4] decimal(10, 2))

insert into @T2008 values ('MMM', 2008, 5.25, 65.25)

declare @T2011 table (id varchar(3), sample_year int, [total_at_t-6] decimal(10, 2), [total_at_t-5] decimal(10, 2))

insert into @T2011 values ('MMM', 2011, 7.25, 98.25)

declare @T2013 table (id varchar(3), sample_year int, [total_at_t-7] decimal(10, 2), [total_at_t-6] decimal(10, 2))

insert into @T2013 values ('MMM', 2013, 3.25, 7.25)

select id, sample_year, null as [total_at_t-7], null as [total_at_t-6], [total_at_t-5], [total_at_t-4]
from @T2008

union

select id, sample_year, null, [total_at_t-6], [total_at_t-5], null
from @T2011

union

select id, sample_year, [total_at_t-7], [total_at_t-6], null, null
from @T2013

How do you align merged data in SQl

1 Answers1