none
PIVOT table for SQL procedures

    Question

  • I have procedure table and have requirement add a pivot function to procedure table, According to i'm tried to add a Pivot function to get a Department code wise against 'CurrentPeriod' Amount, but its getting syntax error, Can you please support to sort out this my concern, for your reference i have uploaded that query file on Midiafire share location. please follow up below Mediafire share location URL and get the current query.

    Query share location-:

    https://www.mediafire.com/file/edqmik43izc2dcl/PROCEDURE.txt/file

    Thanks you

        



    • Edited by Maheshma Thursday, May 16, 2019 1:33 AM
    Wednesday, May 15, 2019 5:03 PM

All replies

  • Hi Team,

    Anybody can support on this?, your support much appreciate.

    Thanks

    Mahesh

    Thursday, May 16, 2019 1:35 AM
  • Hi Maheshma,

     

    Would you please show your query and error messages directly in the reply?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 16, 2019 1:48 AM
  • Hi Dedmon,

    Thank you very much for respond please find the screen shot your requested. if you r need to refer full query you can you follow up below share location and find it same.

    Query share location-:

    https://www.mediafire.com/file/edqmik43izc2dcl/PROCEDURE.txt/file

    Thanks you

    Thursday, May 16, 2019 3:24 AM
  • Good day Maheshma,

    1. You provided a file which include the stored procedure, but how can we test this?!? How can we execute the SP if we do not have the relevant table(s)? Please provide DDL+DML (queries to create the relevant table and insert some sample data)

    2. It is hard in the forum to know what is the level of people that ask question. In one side if we provide answer which fit for expert professor in the field then the person who asked the question (OP) might not understand it, but in the other side if we provide answer that fit for A newbie and the OP is am expert in the field then he might be offended... Please understand that we have no idea what is the level of the person who asked the question

    Sometime a 5 years old child can ask about something that is related to quantum mechanics which professors in the university discuss, and he is sure that we can provide him a short answer and teach him the entire quantum mechanics course in that single response. On the other side the question might come from a professor in the university who is expert to the topic and simply need some help

    My point is that our answer might not fit the OP simply because we are not familiar with the OP.

    I mentioned this since your question and the code might implies that you did not wrote the original SP and I am not sure which part of the solution is not clear to you or if you have the background to answer the question at this time, or maybe it is better to learn some basic before.

    Your code show that you use something like this which make no sense:

    SELECT *
    FROM (
    <here inside a select query you Init Variables, you create table, and so on...>
    )

    Do you understand how to write simple SELECT queries?

    Why did you wrapped your code "as it is" inside a SELECT query?

    Do you know how to implement PIVOT in other simple cases and you simply straggle on how to implement it in this specific case, or do you need help in understand how to use PIVOT in general?

    Do you fully understand the query which you had in the Sp before trying to add the PIVOT? Can post the original code without your attempt to add PIVOT - something that is working well

    Please try to explain what is the exact issue you are facing and what you already know to do. Try to simplified the query and built a simple case which represent the specific issue which you have if you can


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Thursday, May 16, 2019 5:21 AM
    Moderator
  • Hi Ronen,

    Thank you for your guidance, we do not have any method to give a DDL+DML details because we are not developed this SP and this is a system predefined SP, Basically we want to  add pivot table to this SP. as a mentioned earlier. If not Could you please support to add pivot table to this SP? Basically i want to add a department against the amount.

    Thanks

    Mahesh

    Friday, May 17, 2019 9:29 AM
  • Hi Ronen,

    Thank you for your guidance, we do not have any method to give a DDL+DML details because we are not developed this SP and this is a system predefined SP, Basically we want to  add pivot table to this SP. as a mentioned earlier. If not Could you please support to add pivot table to this SP? Basically i want to add a department against the amount.

    Thanks

    Mahesh

    I feel like we speak in two different languages

    * DDL+DML are the queries to create the relevant table and insert some sample data. It has nothing to do with your SP 

    You cannot learn to become expert in one message in the forum. In order to help you I need to know what is your level of knowledge! You did not answer any of my questions😢

    Do you understand how to write simple SELECT queries?

    Do you know how to create table and insert data using queries? If yes, then please provide DDL+DML, if not then please clarify so we will be able to help.

    Do you know how to implement PIVOT in other simple cases and you simply straggle on how to implement it in this specific case, or do you need help in understand how to use PIVOT in general?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, May 17, 2019 8:33 PM
    Moderator
  • Hi Ronen,

    Yes i know how to write a Select Query 

    yes i know how to create table and insert data using queries, but i'm new to SP. Please find the relevant data as a below.

    i know create a simple pivot for select statement. Can you please support to create a Pivot table for below SP.

    Create Tables and Update/ Insert -:

    CREATE PROCEDURE ProfitAndLossReportPeriodAnalysis   
     @Period int,   
     @CompanyPK uniqueidentifier,  
     @DepartmentList varchar(8000),  
     @BranchList varchar(8000),  
     @InclZeroBal char(1),  
     @SummaryType char(20),  
     @IncludeBudget char(1),  
     @ReportType char(20),  
     @TransactionCategory varchar(3),  
     @BranchManagementCode varchar(3) = NULL,
     @AdditionalDissection varchar(10) = ''  
       
    AS  
      
    SET NOCOUNT ON   
      
    DECLARE @PeriodYearBegin AS int, @PeriodYearEnd AS int, @PeriodYear AS int, @PeriodMonth AS int, @AMPeriod AS int, @LastPeriodOfLastYear AS int  
    DECLARE @PeriodLastYearBegin AS int, @PeriodLastYearEnd AS int, @PeriodLastYear AS int, @LastYear AS int  
    DECLARE @BranchCode AS char(3), @BranchName AS nvarchar(50), @DepartmentCode AS char(3), @TTL AS int  
    DECLARE @AccountNumber AS varchar(10), @LastTTLAccNumber AS varchar(10)  
    DECLARE @IntTempVar AS int, @SqlStr AS varchar(5000), @SqlStrCP AS varchar(200), @SqlStrBP AS varchar(200)  
    DECLARE @GrossProfitTotalAccount AS varchar(10), @NetProfitTotalAccount AS varchar(10), @OverHeadTotalAccount AS varchar(10)  
    DECLARE @PLAppropriationAccount AS varchar(10), @Q AS char(1), @REAmount AS money 
    DECLARE @AltAccountsListFromPeriodAnalysis AS varchar(max)
      
    IF @TransactionCategory IS NULL  
     SET @TransactionCategory = ''  
      
    --------------------------------------------------------------------------------------------  
    -- Init Variables  
    --------------------------------------------------------------------------------------------  
    SET @Q = char(39)  
      
    SELECT @GrossProfitTotalAccount = (SELECT AG_AccountNum FROM AccGlHeader  
     WHERE AG_PK = (SELECT CONVERT(uniqueidentifier, CONVERT(nvarchar(4000), CONVERT(varbinary(8000), SD_BinaryValue)))  
     FROM stmdata WHERE sd_name = 'GL_GROSS_PROFIT_TOTAL_ACCOUNT'))  
      
    SELECT @NetProfitTotalAccount = (SELECT AG_AccountNum FROM AccGlHeader 
     WHERE AG_PK = (SELECT CONVERT(uniqueidentifier, CONVERT(nvarchar(4000), CONVERT(varbinary(8000), SD_BinaryValue)))  
     FROM stmdata WHERE sd_name = 'GL_NET_PROFIT_TOTAL_ACCOUNT'))  
      
    SELECT @OverHeadTotalAccount = (SELECT AG_AccountNum FROM AccGlHeader
     WHERE AG_PK = (SELECT CONVERT(uniqueidentifier, CONVERT(nvarchar(4000), CONVERT(varbinary(8000), SD_BinaryValue)))  
     FROM stmdata WHERE sd_name = 'GL_OVERHEAD_TOTAL_ACCOUNT'))  
      
    SELECT @PLAppropriationAccount = (SELECT AG_AccountNum FROM AccGlHeader
     WHERE AG_PK = (SELECT CONVERT(uniqueidentifier, CONVERT(nvarchar(4000), CONVERT(varbinary(8000), SD_BinaryValue)))  
     FROM stmdata WHERE sd_name = 'GL_PL_APPROPRIATION_ACCOUNT'))  
      
    SELECT @PeriodYear = CAST(LEFT(CAST(@Period AS char(6)), 4) AS int)
    SELECT @PeriodMonth = CAST(RIGHT(CAST(@Period AS char(6)), 2) AS int)  
    SELECT @PeriodLastYear = @Period - 100  
      
    SELECT @LastYear = @PeriodYear - 1  
    SELECT TOP 1 @PeriodYearBegin = AM_Period FROM AccPeriodManagement WHERE AM_Year = @PeriodYear ORDER BY AM_Period  
    SELECT TOP 1 @PeriodYearEnd = AM_Period FROM AccPeriodManagement WHERE AM_Year = @PeriodYear ORDER BY AM_Period DESC  
    SELECT TOP 1 @PeriodLastYearBegin = AM_Period FROM AccPeriodManagement WHERE AM_Year = @LastYear ORDER BY AM_Period  
    SELECT TOP 1 @PeriodLastYearEnd = AM_Period FROM AccPeriodManagement WHERE AM_Year = @LastYear ORDER BY AM_Period DESC  
      
    --------------------------------------------------------------------------------------------  
    -- Create Temp Tables  
    --------------------------------------------------------------------------------------------  
    CREATE TABLE #PLPERIODS  

     AccountPK UNIQUEIDENTIFIER, 
     AccountNumber varchar(10) COLLATE database_default,  
     AccountName varchar(35) COLLATE database_default,  
     AccountType char(3) COLLATE database_default,  
     PrintSequence int,  
     AccountNumberForSequence varchar(14) COLLATE database_default,  
     AdditionalDissection char(3) COLLATE database_default,
     HeaderDependsOnTotal UNIQUEIDENTIFIER
    )  
      
    CREATE TABLE #PLSINGLEPERIOD  
    (  
     AccountPK uniqueidentifier,  
     AccountNumber nvarchar(40) COLLATE database_default,  
     AccountNumberForTotals nvarchar(40) COLLATE database_default,  
     AccountNumberForSequence nvarchar(40) COLLATE database_default,  
     PrintSequence int,  
     AccountName nvarchar(160) COLLATE database_default,  
     AccountType char(3) COLLATE database_default,  
     SectionType char(2) COLLATE database_default,  
     DebitCredit char(2) COLLATE database_default,  
     CurrentPeriod money,  
     YearToPeriod money,  
     PeriodLastYear money,  
     LastYearToPeriod money,  
     TotalLastYear money,  
     BudgetCurrentPeriod money,  
     BudgetYearToPeriod money,  
     BudgetLastYearTotal money,  
     TotalYearBudget money,  
     TTL int,  
     PercentageAccount varchar(10) COLLATE database_default,  
     ConsolidateAccount varchar(10) COLLATE database_default,  
     AlternateAccount varchar(10) COLLATE database_default,  
     BranchPK uniqueidentifier,  
     BranchCode char(3) COLLATE database_default,  
     BranchName nvarchar(50) COLLATE database_default,  
     DepartmentPK uniqueidentifier,  
     DepartmentCode char(3) COLLATE database_default,  
     DepartmentName varchar(35) COLLATE database_default,  
     PercentageAccountPK uniqueidentifier,  
     ConsolidateAccountPK uniqueidentifier,  
     AlternateAccountPK uniqueidentifier,  
     CurrentDebit money,  
     CurrentCredit money,  
     ClosingDebit money,  
     ClosingCredit money,  
     CurrentYearMovement money,  
     GLAccountNum varchar(10) COLLATE database_default,  
     AccountNumGroupHeader varchar(10) COLLATE database_default,  
     CanRollUp char(1) COLLATE database_default,  
     BranchMgtCode VARCHAR(3) COLLATE database_default,
     IsGroupedByMgtCode INT DEFAULT(0),
    )  
      
    --------------------------------------------------------------------------------------------  
    -- Add P0 Column (Opening Balance) to #PLPERIODSUPDATE for Trial Balance  
    --------------------------------------------------------------------------------------------  
      
    IF @ReportType = 'TBS'  
    BEGIN  
     ALTER TABLE #PLPERIODS ADD P0 money  
    END  
      
    --------------------------------------------------------------------------------------------  
    -- Add Columns to #PLPERIODS  
    --------------------------------------------------------------------------------------------  
    SELECT @IntTempVar = 1  
    DECLARE Cursor1 CURSOR FOR SELECT AM_Period FROM AccPeriodManagement WHERE AM_Year = @PeriodYear AND AM_GC_COMPANY = @CompanyPK ORDER BY AM_Period   
    OPEN Cursor1  
    FETCH NEXT FROM Cursor1 INTO @AMPeriod  
    WHILE (@@FETCH_STATUS <> -1)  
    BEGIN  
     IF @IncludeBudget = 'Y'  
     BEGIN  
      SELECT @SqlStr = 'ALTER TABLE #PLPERIODS ADD P' + CAST(@IntTempVar AS char(2)) + ' money, B' + CAST(@IntTempVar AS char(2)) + ' money'  
     END  
     ELSE  
     BEGIN  
      SELECT @SqlStr = 'ALTER TABLE #PLPERIODS ADD P' + CAST(@IntTempVar AS char(2)) + ' money'  
     END  
      
     EXEC (@SqlStr)  
       
     SELECT @IntTempVar = @IntTempVar + 1  
     FETCH NEXT FROM Cursor1 INTO @AMPeriod  
    END  
    CLOSE Cursor1  
    DEALLOCATE Cursor1

    --------------------------------------------------------------------------------------------  
    -- Add a ClosingBalance to #PLPERIODS  
    --------------------------------------------------------------------------------------------  
    ALTER TABLE #PLPERIODS 
    ADD CLOSINGBALANCE money

    --------------------------------------------------------------------------------------------  
    -- UPDATE #BRANCH, #DEPARTMENT  
    --------------------------------------------------------------------------------------------  
    SELECT DISTINCT AA_GB AS GBPK, GB_Code AS BranchCode, GB_BranchName AS BranchName
     INTO #BRANCH
     FROM AccGlAggregate
     JOIN GlbBranch ON GB_PK = AA_GB  
     WHERE AA_Period = @Period AND GB_GC = @CompanyPK  
     AND (AA_TransactionCategory = @TransactionCategory OR AA_TransactionCategory = '')  
        
    SELECT DISTINCT AA_GE AS GEPK, GE_Code AS DepartmentCode, GE_Desc AS DepartmentName
     INTO #DEPARTMENT
     FROM AccGlAggregate  
     JOIN GlbDepartment ON GE_PK = AA_GE  
     WHERE AA_Period = @Period  
     AND (AA_TransactionCategory = @TransactionCategory OR AA_TransactionCategory = '')  
      
    --------------------------------------------------------------------------------------------  
    -- SET variables  
    --------------------------------------------------------------------------------------------  
    IF @ReportType = 'PNL'  
    BEGIN  
    IF @IncludeBudget = 'Y'
    BEGIN
    INSERT INTO #PLPERIODS (AccountPK, AccountNumber, AccountName, AccountType, PrintSequence, HeaderDependsOnTotal)
    SELECT AG_PK, AG_AccountNum, AG_Description, AG_AccountType, AG_PrintSequence, AG_AG_HeaderDependsOnTotal
    FROM AccGLHeader WHERE AG_Column IN ('TS', 'OV', 'AP') AND AG_AccountType NOT IN ('BSH', 'ALT')
    ORDER BY AG_AccountNum
    END
    SET @SqlStrCP = '#PLSINGLEPERIOD.CurrentPeriod'  
    SET @SqlStrBP = ' = #PLSINGLEPERIOD.BudgetCurrentPeriod '  
    END  
    ELSE IF @ReportType = 'BSH'  
    BEGIN  
     SET @SqlStrCP = '#PLSINGLEPERIOD.YearToPeriod'  
     SET @SqlStrBP = ' = #PLSINGLEPERIOD.BudgetYearToPeriod '  
    END  
    ELSE IF @ReportType = 'TBS'  
    BEGIN  
    INSERT INTO #PLPERIODS (AccountPK, AccountNumber, AccountName, AccountType, PrintSequence, HeaderDependsOnTotal)
    SELECT AG_PK, AG_AccountNum, AG_Description, AG_AccountType, AG_PrintSequence, AG_AG_HeaderDependsOnTotal
    FROM AccGLHeader
    WHERE AG_AccountType NOT IN ('TTL', 'CLN', 'HDR')
    ORDER BY AG_AccountNum

     SET @SqlStrCP = '#PLSINGLEPERIOD.CurrentPeriod * -1'  
     SET @SqlStrBP = ' = #PLSINGLEPERIOD.BudgetYearToPeriod * -1 '  
    END  

    ---------------------------------------------------------------------------------------------------
    -- Create list of ALT accounts for ProfitAndLossReport stored procedure to use in previous periods
    ---------------------------------------------------------------------------------------------------
    IF @Period IS NOT NULL
    BEGIN
    SET @AltAccountsListFromPeriodAnalysis = ''
    IF @IncludeBudget = 'Y'  
      BEGIN
        INSERT INTO #PLSINGLEPERIOD EXEC ProfitAndLossReport @Period, @CompanyPK, @DepartmentList, @BranchList, 'Y', '', 'Y', @ReportType, '', '', '', '', '', '', @TransactionCategory, @BranchManagementCode, 'Y'        
        SELECT @SqlStr = 'UPDATE #PLPERIODS SET' + 
         ' P' + CAST(@PeriodMonth AS char(2)) + ' = ' + @SqlStrCP + 
         ', B' + CAST(@PeriodMonth AS char(2)) + @SqlStrBP  + 
         ' FROM #PLSINGLEPERIOD INNER JOIN #PLPERIODS ON #PLPERIODS.AccountNumber = #PLSINGLEPERIOD.AccountNumber'  
      END  
    ELSE  
      BEGIN
        INSERT INTO #PLSINGLEPERIOD EXEC ProfitAndLossReport @Period, @CompanyPK, @DepartmentList, @BranchList, 'Y', '', '', @ReportType, '', '', '', '', @AdditionalDissection, '', @TransactionCategory, @BranchManagementCode, 'N'      
        INSERT INTO #PLPERIODS (AccountPK, AccountNumber, AccountName, AccountType, PrintSequence, AdditionalDissection, HeaderDependsOnTotal)  
        (SELECT AG_PK, AG_AccountNum, AG_Description, AG_AccountType, AG_PrintSequence,
            CASE  
      WHEN @AdditionalDissection = '' THEN ''  
      WHEN @AdditionalDissection = 'Branch' THEN ISNULL(BranchCode, '')  
      WHEN @AdditionalDissection = 'Department' THEN ISNULL(DepartmentCode, '') 
    END AS AdditionalDissection,
        AG_AG_HeaderDependsOnTotal  
     FROM AccGLHeader INNER JOIN #PLSINGLEPERIOD ON AG_AccountNum = AccountNumber  

         UNION  
      
         SELECT AG_PK, AG_AccountNum, AG_Description, AG_AccountType, AG_PrintSequence, '', AG_AG_HeaderDependsOnTotal
     FROM AccGLHeader INNER JOIN #PLSINGLEPERIOD ON AG_AccountNum = AccountNumber AND (@AdditionalDissection = 'Branch' AND BranchCode IS NOT NULL OR @AdditionalDissection = 'Department' AND DepartmentCode IS NOT NULL)  
        )  

        EXCEPT  

        SELECT AccountPK, AccountNumber, AccountName, AccountType, PrintSequence, AdditionalDissection, HeaderDependsOnTotal FROM #PLPERIODS
          
        SELECT @SqlStr = 'UPDATE #PLPERIODS SET P' + CAST(@PeriodMonth AS char(2)) + ' = Amount, CLOSINGBALANCE = ISNULL(CLOSINGBALANCE, 0.00) + ISNULL(Amount, 0.00)   
      FROM   
      (SELECT ' + @SqlStrCP + ' AS Amount, #PLSINGLEPERIOD.AccountNumber ' + CASE WHEN @AdditionalDissection = '' THEN '' ELSE ', ' + 
       @AdditionalDissection + 'Code AS AdditionalDissection' END + ' FROM #PLSINGLEPERIOD ' + CASE WHEN @AdditionalDissection = '' THEN '' ELSE '   
       UNION         
       SELECT SUM(' + @SqlStrCP + ') AS Amount, #PLSINGLEPERIOD.AccountNumber, '''' AS AdditionalDissection  
       FROM #PLSINGLEPERIOD  
       GROUP BY #PLSINGLEPERIOD.AccountNumber' END + '  
       ) InnerSelect INNER JOIN #PLPERIODS ON #PLPERIODS.AccountNumber = InnerSelect.AccountNumber' + CASE WHEN @AdditionalDissection = '' THEN '' ELSE ' AND #PLPERIODS.AdditionalDissection = InnerSelect.AdditionalDissection' END  
      END  
    EXEC (@SqlStr)
    IF @ReportType = 'BSH'
    BEGIN
      SELECT @AltAccountsListFromPeriodAnalysis = @AltAccountsListFromPeriodAnalysis + AccountNumber + ',' FROM #PLPERIODS WHERE AccountType = 'ALT' AND CLOSINGBALANCE <> 0.00  
    END
    IF @ReportType = 'TBS'
    BEGIN
      SELECT @AltAccountsListFromPeriodAnalysis = @AltAccountsListFromPeriodAnalysis + AccountNumber + ',' FROM #PLSINGLEPERIOD WHERE (AccountType = 'ALT' AND DebitCredit = 'CR' AND YearToPeriod IS NOT NULL AND ClosingCredit IS NOT NULL)
        OR    (AccountType = 'ALT' AND DebitCredit = 'DR' AND YearToPeriod IS NOT NULL AND ClosingDebit IS NOT NULL)  
    END
    IF LEN(@AltAccountsListFromPeriodAnalysis) > 0
      SET @AltAccountsListFromPeriodAnalysis = SUBSTRING(@AltAccountsListFromPeriodAnalysis, 1, LEN(@AltAccountsListFromPeriodAnalysis) - 1)
    END

    **************************************************************************************

    There have a few more Update and insert statement according to below i attached full SP query for your reference.

    For your reference please find the Full SP Query

    Full  SP Query-:

    https://www.mediafire.com/file/tdt57kcngv3xi1z/PROCEDURE_ProfitAndLossReportPeriodAnalysis.txt/file

    Thanks

    Mahesh

    Tuesday, May 21, 2019 4:48 AM
  • hi,

    Why do you want to add PIVOT action?!?
    To what do you want to add PIVOT?!?

    You have a SP which do a lot of actions and one day someone told you to add PIVOT. You said that you "know create a simple pivot for select statement" and yet in the code you ad this:

    	pivot  
    	(  
    	   Sum(Counts) for Article in ([Sql Server],[MVC],[C#])  
    	) as PivotTable

    After IF statement, without any relation to any SELECT. You have something like |IF BEGIN <something come here> END PIVOT...". THIS TOTALLY MAKE NO SENSE and it was not written by someone who understand basic use if "IF" and basic use of "PIVOT".

    Whats is the query which need to have PIVOT?!? Why do you want to add PIVOT action?!? To what do you want to add PIVOT?!?

    Forget about all the SP and all the irrelevant actions inside the SP! focus on the query which you want to use PIVOT (if there is any). Post only the relevant SELECT query which need to have PIVOT

    If you do not understand the code you have, then first learn the code statement by statement! It might be good idea (if this is an option) to get first help from the person who actually wrote this code.

    YOU STILL DID NOT PROVIDED THE DDL+DML!!

    ---------------- Off topic ------------

    > Four times you select from the same table AccGlHeader, and each time you select from another table stmdata in the filter condition using sub-query. This seems VERY problematic regarding performance.

    1. Why not doing it all in one query?!?

    The only differences between these queries is in the filter condition :
    WHERE sd_name = 'GL_GROSS_PROFIT_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_NET_PROFIT_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_OVERHEAD_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_PL_APPROPRIATION_ACCOUNT'

    Why not getting the result of all these 4 conditions into CTE, next from the CTE you use simple PIVOT input all the values in one time to all 4 variables?

    You know that you can use SELECT in order to SET multiple variables and you do not have to do it one by one.

    You should check this option!

    Unfortunately, since we have no information regarding the tables structure and the indexes, I cannot confirm the performance in your case, but if you don't have indexes which fit for "index seek" then this mean scanning the table/index once instead of 4 times as you do right now.

    YOUR ENTIRE SP SEEMS IN FIRST GLANCE, LIKE COLLECTION of PIECES of CODE WHICH SOMEONE PUT TOGETHER WITHOUT ANY GLOBAL VIEW. Now you want to add another patch of code...

    For example the value of @IncludeBudget comes from the input to the SP, next you create table, next you ALTER the table according to the value of @IncludeBudget using dynamic query... why not simply create the table in the right way from the start according to input of @IncludeBudget?!?

    Why not add the column CLOSINGBALANCE from the start?

    You use CURSOR in order to build the dynamic SQl which can be done using SET approach like using the function STRING_AGG or using "FOR XML" in order to aggregate the information from all rows into one string.

    ... and so on...

    > You use select from table in order to input value to a variable. What if the result SET returns more than one row for specific condition?

    >> We still have no DDL+DML for the tables!
    AccGlHeader , stmdata 

    But at this time it seems like you don't understand the code which you already have, which mean that there is no starting point to help to change it and understand the solution.

    And once more, Whats is the query which need to have PIVOT?!? Why do you want to add PIVOT action?!? To what do you want to add PIVOT?!? Forget about all the SP and all the irrelevant actions inside the SP! focus on the query which you want to use PIVOT (if there is any). Post only relevant SELECT query which need to have PIVOT


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, May 21, 2019 12:59 PM
    Moderator
  • Hi Ronen,

    Actually in the attached current Query passing entire company Profit and loss details as GL account brake down and that Group as a Gross profit, OverHeades, Finally Net Profit.(For your idea here with i'll attach structure i'm expect) I want to that full details split as a Department wise, Currently department available on select statement so i want to get that department code as a table columns headings and that department wise details breakdown as a GL account number, That why i require PIVOT table for this query,

    1.This is a system defined SP.And as my knowledge they have create that four conditions for get grouping totals as a Gross profit, Overhead, Net profit etc,

    WHERE sd_name = 'GL_GROSS_PROFIT_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_NET_PROFIT_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_OVERHEAD_TOTAL_ACCOUNT'
    WHERE sd_name = 'GL_PL_APPROPRIATION_ACCOUNT'

    2.For your above request all details attached on the share location,

    http://www.mediafire.com/folder/m4sumjuhvbfuq/P

    Thanks

    Mahesh

    Sunday, May 26, 2019 3:21 PM
  • Hi Ronen,

    Have you any chance to look into my update?.

    Thanks

    Mahesh

    Tuesday, May 28, 2019 8:34 AM
  • Anyone has an opinion?
    Thursday, May 30, 2019 8:23 AM
  • Dear Team,

    Anybody can support on this.. Your support much appreciate.

    Thanks


    • Edited by Maheshma Sunday, June 2, 2019 5:19 PM
    Sunday, June 2, 2019 5:17 PM