There are two tables with the following views:

 tbl_invoice
 ----------------------------------
 inv_id |  inv_code |  inv_nettotal |
 ----------------------------------
   1 |  inv_1 |  2000 |
   2 |  inv_2 |  3000 |
 ----------------------------------

 tbl_payment
 ------------------------------------------
 p_id |  inv_id |  p_date |  p_amount |
 -------------------------------------------
   1 |  2 |  01/01/2016 |  1000 |
   2 |  1 |  02/08/2016 |  1500 |
   3 |  2 |  02/10/2016 |  1500 |
   4 |  1 |  02/10/2016 |  500 |
 -------------------------------------------

We need to integrate them so that we get the following:

 -------------------------------------------------- --------------------------------------
 |  inv_id |  inv_code |  inv_nettotal |  01/01/2016 |  02/08/2016 |  02/10/2016 |  amount_due |
 -------------------------------------------------- --------------------------------------
 |  1 |  inv_1 |  2000 |  0 |  1500 |  500 |  0 |
 |  2 |  inv_2 |  3000 |  1000 |  0 |  1500 |  500 |
 -------------------------------------------------- --------------------------------------
  • 2
    join tables and pivot transformation - Akina
  • And primerchik possible? - Dowlpack
  • google: "mssql pivot" I’ll warn you right away that all columns in SQL should be described in advance, so it won’t be released dynamically. Only dynamic formation of request. In general, it makes sense to think about expanding to the horizontal already on the client - Mike

1 answer 1

It turns out a rather complicated script consisting of dynamic column formation for PIVOT, as well as for other operations. It should be noted that it is formed as a string and the final script will eventually also form a string.

The result is such a Frankenstein

declare @DynamicPivotQuery as nvarchar(max) declare @ColumnName as nvarchar(max) declare @ColumnNameMax as nvarchar(max) declare @ColumnNameSum as nvarchar(max) select @ColumnName= isnull(@ColumnName + ',','') + quotename(p_date) from (select distinct p_date from tbl_payment) as Dates select @ColumnNameMax= isnull(@ColumnNameMax + ',','') + 'max(' + quotename(p_date) + ') as ' + quotename(p_date) from (select distinct p_date from tbl_payment) as Dates select @ColumnNameSum = isnull(@ColumnNameSum + '+','') + 'isnull(' + quotename(p_date) + ', 0)' from (select distinct p_date from tbl_payment) as Dates set @DynamicPivotQuery = N' select inv.inv_id, inv.inv_code, inv.inv_nettotal,' + @ColumnName + ', inv.inv_nettotal - ('+@ColumnNameSum+') as amount_due from tbl_invoice as inv left join ( select inv_id, ' + @ColumnNameMax + ' from tbl_payment pivot(sum(p_amount) for p_date in (' + @ColumnName + ')) as PVTTable group by inv_id) as pay on pay.inv_id = inv.inv_id' exec sp_executesql @DynamicPivotQuery

Of course, the script with the expansion of columns in the horizontal is much better when the names are known in advance. That's how it would look if the dates were fixed. If your application has the ability to define them before sending to the script execution and dynamically generate it, it is better to use it. Or choose a different approach in forming the totals on the client.

 select inv.inv_id, inv.inv_code, inv.inv_nettotal, [2016-01-01], [2016-02-08], [2016-02-10], inv.inv_nettotal - (isnull([2016-01-01], 0)+isnull([2016-02-08], 0)+isnull([2016-02-10], 0)) as amount_due from tbl_invoice as inv left join ( select inv_id, max([2016-01-01]) as [2016-01-01],max([2016-02-08]) as [2016-02-08],max([2016-02-10]) as [2016-02-10] from tbl_payment pivot(sum(p_amount) for p_date in ([2016-01-01],[2016-02-08],[2016-02-10])) as PVTTable group by inv_id) as pay on pay.inv_id = inv.inv_id
  • Thank you friend! A little sub-correct and everything fits perfectly. What you need) - Dowlpack