CURSOR for column in WHERE that gives dynamic columns in SELECT

Question

Hello,

I need help to make my query more simple

Depends how many rows for column Code (now I have 53 rows) then it will be 53 sub selects and 53 more columns.

How to make it simple to add variable in select and in sub select instead of writing 53 times as I did?

Thank you.

  select a.acct Account_Code
  , case when a100.Debit_Balance is null then a100.Credit_Balance else a100.Debit_Balance end [100] 
  , case when a102.Debit_Balance is null then a102.Credit_Balance else a102.Debit_Balance end [102] 
  , case when a109.Debit_Balance is null then a109.Credit_Balance else a109.Debit_Balance end [109] 
  , case when a110.Debit_Balance is null then a110.Credit_Balance else a110.Debit_Balance end [110] 
  , case when a111.Debit_Balance is null then a111.Credit_Balance else a111.Debit_Balance end [111] 
  , case when a112.Debit_Balance is null then a112.Credit_Balance else a112.Debit_Balance end [112] 
  , case when a115.Debit_Balance is null then a115.Credit_Balance else a115.Debit_Balance end [115] 
  -------------------------------------------------------------------------------------
  from 
  (
  SELECT    [Account_Code] acct
 From  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]              
 union  
   SELECT    [Account_Code] acct
 From dbo.[2023_Revenues_Run_10_18_24]     ) a
 left join   --------------------------------------------------------------------------------
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=100 ) a100
   on a.acct=a100.[Account_Code]
left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=102 ) a102
   on a.acct=a102.[Account_Code]
left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=109 ) a109
   on a.acct=a109.[Account_Code]
left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=110 ) a110
   on a.acct=a110.[Account_Code]
left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=111 ) a111
   on a.acct=a111.[Account_Code]
left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=112 ) a112
   on a.acct=a112.[Account_Code]
   left join
  ( select [Code], [Account_Code], [Account_Title], [Debit_Balance], [Credit_Balance] from  ( 
     select * from  dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]  union  select  * from  dbo.[2023_Revenues_Run_10_18_24] ) a   where  a.code=115 ) a115
   on a.acct=a115.[Account_Code]
   order by 1

Answer 1

Here is a typical pivot query you can use:

SELECT Account_Code, 
       MIN(CASE WHEN code = 100 THEN isnull(Credit_balance, Debit_balance) END) AS [100],
       MIN(CASE WHEN code = 102 THEN isnull(Credit_balance, Debit_balance) END) AS [102],
       ...
FROM   (SELECT Code, Account_Code, Credit_balance, Debit_balance
        FROM   dbo.[2023_TB_Expenses_not_including_Salaries_FB_Run_10_15_24]
        UNION ALL 
        SELECT Code, Account_Code, Credit_balance, Debit_balance
        FROM   dbo.[2023_Revenues_Run_10_18_24]) AS u
GROUP  BY Account_Code

Note that I have replaced UNION with UNION ALL. UNION will remove duplicates in the set, which I don't think you want here.