Dynamic column result data

• kasimacsys

  SSC Rookie

  Points: 32

  More actions

  September 15, 2022 at 10:05 am

  #4088002

  Hi,

  First of all pls don't comment on table structure. its already created we cannot change anything.

  I have 3 table like below. ##Invoice (which has the unique data) and ##ReInvoice (we will submit until its get fully paid. here we maintain level) and ##Payment (which has the payment history also maintain level)

  create table ##Invoice (ID int, InvDate Date, Amount decimal(18,2))

  insert into ##Invoice values (1001,'2022-01-01',1000);

  create table ##ReInvoice (ID int, ReInvDate Date, Amount decimal(18,2), InvLevel int)

  insert into ##ReInvoice values (1001,'2022-01-03',800,2)

  insert into ##ReInvoice values (1001,'2022-01-05',500,3)

  create table ##Payment (ID int, PayDate Date, Amount decimal(18,2), PayLevel int)

  insert into ##Payment values (1001,'2022-01-02',200,1);

  insert into ##Payment values (1001,'2022-01-04',300,2);

  insert into ##Payment values (1001,'2022-01-06',400,3);

  create table ##Result (ID int, InvDate Date, Amount decimal(18,2), PaidAmount decimal(18,2), Balance decimal(18,2), PaymentStatus nvarchar(20),

  _1PaidDate Date, _1PaidAmount decimal(18,2),

  _1ReInvDate Date, _1ReInvAmount decimal(18,2), _2PaidDate Date, _2PaidAmount decimal(18,2),

  _2ReInvDate Date, _2ReInvAmount decimal(18,2), _3PaidDate Date, _3PaidAmount decimal(18,2))

  insert into ##Result values (1001,'2022-01-01',1000,900,100,'Partial','2022-01-02',200,'2022-01-03',800,'2022-01-04',300,'2022-01-05',500,'2022-01-06',400);

  --Status would be 'Open/Paid/Partial

  select from ##Invoice;

  select from ##ReInvoice;

  select from ##Payment;

  select from ##Result;

  drop table ##Invoice

  drop table ##ReInvoice

  drop table ##Payment

  drop table ##Result

   

  Attachments:

  You must be logged in to view attached files.

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  September 15, 2022 at 12:11 pm

  #4088048

  because I don't like sequence numbers in column names ...

  Declare @pvtColumns nvarchar(max) = ''
  Select @pvtColumns= stuff(
     (
             Select '['+convert(char(10),PayDate, 121) + '],'
  from #Payment 
  group by PayDate 
             order by PayDate
             for xml path('P'), root('R'), type
             ).query('R/P').value('.', 'varchar(64)'), 1, 0, '') 
  Select @pvtColumns = substring(@pvtcolumns,0,(datalength(@pvtcolumns)/2))
  Declare @DynSQL nvarchar(max) ='';
  Select  @DynSQL = '
  Select *
  from (
  SELECT I.ID, I.invdate, I.amount, P.PayDate, P.amount as PayAmount, sum(P.amount) over (partition by I.id ) as TotalPayed
  FROM #Invoice AS I
  INNER JOIN #Payment AS P
   ON P.ID = I.ID
  /*order by I.ID, I.invdate, P.PayDate*/
  ) Results
  PIVOT(  sum ( PayAmount )   FOR  Results.PayDate IN ( '+@pvtColumns+' )
  ) as pvt
  '
  
  exec sp_executesql @stmt = @DynSQL ;
  

  Test it !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me