CURSOR for column in WHERE that gives dynamic columns in SELECT

Alan5896 26 Reputation points
2024-10-20T20:25:46.2966667+00:00

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.

table1

table2

table3

  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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,061 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,661 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 112.8K Reputation points MVP
    2024-10-20T21:15:53.5933333+00:00

    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.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.