sql server 2008 - SQL Multiple dynamic queries to one query -

i working in sql server 2008 environment sql server management studio 2012.

i have written 3 separate queries

query 1

sql query sums stock on hand inventories table

select      stockcode,             sum(qtyonhand) 'soh'        invwarehouse       stockcode not '%dem%' , stockcode not '%ref%' , stockcode not 'z%' group    invwarehouse.stockcode 

query 2

this query looks @ future orders purchase orders table , dynamically returns next/following 12 months

declare  @cols nvarchar(max), @cols1 nvarchar(max), @cols2 nvarchar(max), @cols3 nvarchar(max), @query nvarchar(max)  select @cols = stuff((select ',' + quotename(yearmonth)                                     -- selecting using destinct --                      (select distinct cast(year([orderduedate]) nvarchar(4)) + right('00' + cast(month([orderduedate]) nvarchar(2)),2) yearmonth                     pormasterhdr                      join pormasterdetail                      on pormasterdetail.purchaseorder = pormasterhdr.purchaseorder                     datediff(month, orderduedate, dateadd(m,12,getdate())) <= 12 ) sub                      order yearmonth                    xml path(''), type).value('.', 'nvarchar(max)'),1,1,''),     @cols2 = stuff((select ',isnull(' + quotename(yearmonth) + ',0) ' + quotename(yearmonth)                                       -- selecting using destinct --                      (select distinct cast(year([orderduedate]) nvarchar(4)) + right('00' + cast(month([orderduedate]) nvarchar(2)),2) yearmonth                     pormasterhdr                     join pormasterdetail                      on pormasterdetail.purchaseorder = pormasterhdr.purchaseorder                     datediff(month, orderduedate, dateadd(m,12,getdate())) <= 12) sub                      order yearmonth                     xml path(''), type).value('.', 'nvarchar(max)'),1,1,'')   set @query = ' select mstockcode, ' + @cols2 + '     (         select      mstockcode,                      morderqty,                      cast(year([orderduedate]) nvarchar(4))+right(''00''+cast(month([orderduedate]) nvarchar(2)),2) yearmonth         pormasterhdr         join pormasterdetail          on pormasterdetail.purchaseorder = pormasterhdr.purchaseorder               mstockcode not ''%dem%'' , mstockcode not ''%ref%'' , mstockcode not ''z%''         ) x   pivot   (         sum(morderqty)         yearmonth in (' + @cols + ')         ) pt'  execute (@query) 

query 3

this query looks @ past 12 month of sales data sales table , dynamically returns last/previous 12 months

declare  @cols nvarchar(max), @cols1 nvarchar(max), @cols2 nvarchar(max), @cols3 nvarchar(max), @query nvarchar(max)  select @cols = stuff((select ',' + quotename(yearmonth)                                     -- selecting using destinct --                      (select distinct cast([trnyear] nvarchar(4)) + right('00' + cast([trnmonth] nvarchar(2)),2) yearmonth                     artrndetail                     datediff(month, invoicedate, getdate()) <= 12 ) sub                      order yearmonth                    xml path(''), type).value('.', 'nvarchar(max)'),1,1,''),     @cols2 = stuff((select ',isnull(' + quotename(yearmonth) + ',0) ' + quotename(yearmonth)                                       -- selecting using destinct --                      (select distinct cast([trnyear] nvarchar(4)) + right('00' + cast([trnmonth] nvarchar(2)),2) yearmonth                     artrndetail                     datediff(month, invoicedate, getdate()) <= 12) sub                      order yearmonth                     xml path(''), type).value('.', 'nvarchar(max)'),1,1,'')   set @query = ' select stockcode, ' + @cols2 + '     (         select      stockcode,                      qtyinvoiced,                      cast([trnyear] nvarchar(4))+right(''00''+cast([trnmonth] nvarchar(2)),2) yearmonth                artrndetail               stockcode not ''%dem%'' , stockcode not ''%ref%'' , stockcode not ''z%''         ) x   pivot   (         sum(qtyinvoiced)         yearmonth in (' + @cols + ')         ) pt'  execute (@query) 

the results each query correct. how combine them 1 query. return

stockcode     |   past 12 month sales per month   |  stock on hand  |  future purchases  helicopters   | 1  4  5  2  3  4  6  1  3  2  3  2|         15      |  2  3  5  4  6  7  8  4  3  2  8  5 jam           | 2  5  6  4  8  5  8  5  7  2  1  2|         30      |  4  5  6  5  8  7  0  1  2  1  1  4 frogs         | 2  3  2  4  8  5  4  6  8  2  1  3|          7      |  5  7  8  8  6  7  4  0  1  2  1  2 

stock code above same information different tables eg. helicopters in inventory same helicopters in purchase orders.

i suggest following:

1. rewrite @query2 result in 2 columns: stockcode , sales. instead of selecting each month seperate column, concatenate each month in varchar. wrote variable @cols selecting columns seperately. keep pivoting. write variable (@selsales) concatenate results each month in varchar , use in selection sales column.
2. rewrite @query3 result in 2 columns: stockcode , purchases (similar 1.)
3. put @query1 in nvarchar(max) variable (the 1 selecting stock).
4. write @query combine them all.

tsql outline @query:

declare @query nvarchar(max); set @query=n' select   coalesce(stock.stockcode,sales.stockcode,purchases.stockcode) stockcode,   coalesce(sales.sales,''0 0 0 0 0 0 0 0 0 0 0 0'') sales,   coalesce(stock.soh,0) stock,   coalesce(purchases.purchases,''0 0 0 0 0 0 0 0 0 0 0 0'') purchases   ('+@query1+') stock   full join ('+@query2+') sales on sales.stockcode=stock.stockcode   full join ('+@query3+') purchases on purchases.stockcode=stock.stockcode'; exec(@query);