February 8, 2021 at 8:45 pm
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'
February 9, 2021 at 9:10 pm
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
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://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
February 9, 2021 at 9:37 pm
result after executing the sql
result wishes
February 15, 2021 at 11:01 am
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:
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.
Regards
VG
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply