Pivot Table SQL
-
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'
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
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
-
result after executing the sql
result wishes
-
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:
- First, group the data ( dbo.ProjForecastSalesStaging ) by PROJECTID, MODELID, PROJECTLINEPROPERTYID, PROJECTFORECASTINOICEDATE and insert that data into a table
- 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.
Regards
VG
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply