Post reply

Pivot Table SQL

  • February 8, 2021 at 8:45 pm

    #3842641

    I would like your support to find a solution for my problem of duplicating several lines instead of being in a single line after executing my SQL code

    I show my SQL code and a file of the result after executing the sql code and the result wishes

    thank you

    IF OBJECT_ID('dbo.TBL_TEMP') IS NOT NULL

    DROP TABLE TBL_TEMP

    DECLARE @sqlquery AS NVARCHAR(MAX)

    DECLARE @PivotColumns AS NVARCHAR(MAX)

    SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(CONVERT(DATE,PROJECTFORECASTINVOICEDATE))

    FROM [dbo].[ProjForecastSalesStaging]

    WHERE [MODELID] = 'Cash flow'

    GROUP BY CONVERT(DATE,PROJECTFORECASTINVOICEDATE)

    ORDER BY CONVERT(DATE,PROJECTFORECASTINVOICEDATE) ASC

    SET @sqlquery =

    N'SELECT [PROJECTID],[MODELID],[PROJECTLINEPROPERTYID],' + @PivotColumns + '

    INTO TBL_TEMP

    FROM [dbo].[ProjForecastSalesStaging]

    PIVOT ( SUM(AMOUNT)

    FOR PROJECTFORECASTINVOICEDATE IN (' + @PivotColumns + ') ) AS Q

    WHERE Q.[MODELID] = ''Cash flow''

    GROUP BY [PROJECTID],[MODELID],[PROJECTLINEPROPERTYID],' + @PivotColumns + ' '

    EXEC sp_executesql @sqlquery

    Select TBL_TEMP.*

    from TBL_TEMP WHERE PROJECTLINEPROPERTYID='011400'

    Attachments:
    You must be logged in to view attached files.

  • February 9, 2021 at 9:10 pm

    #3843098

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 9, 2021 at 9:27 pm

    #3843103

    People tend to be wary of opening attachments, especially from new users. Can you put the expected result into a post?

    It might also help if you can provide test data as detailed here https://qa.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

     

  • February 9, 2021 at 9:37 pm

    #3843110

    result after executing the sql

    result after executing the sql

    result wishes

     

    result wishes

  • February 15, 2021 at 11:01 am

    #3845162

    Below query needs change as per my interpretation:

    SET @sqlquery =
    N'SELECT [PROJECTID],[MODELID],[PROJECTLINEPROPERTYID],' + @PivotColumns + '
    INTO TBL_TEMP
    FROM [dbo].[ProjForecastSalesStaging]

    PIVOT ( SUM(AMOUNT)
    FOR PROJECTFORECASTINVOICEDATE IN (' + @PivotColumns + ') ) AS Q
    WHERE Q.[MODELID] = ''Cash flow''
    GROUP BY [PROJECTID],[MODELID],[PROJECTLINEPROPERTYID],' + @PivotColumns + ' '

    You can achieve the output of following method:

    1. First, group the data ( dbo.ProjForecastSalesStaging ) by PROJECTID, MODELID, PROJECTLINEPROPERTYID, PROJECTFORECASTINOICEDATE and insert that data into a table
    2. Then, write the logic of PIVOT in the grouped data created in step-1.

    I think that it should help you. If it does not work, please share table scripts along with schema and sample data. It can retried.

    • This reply was modified 3 years, 7 months ago by  GroverVivek.

    Regards
    VG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply