Pivot query is executing but no data return,please have a look

Transact-SQL
1

DECLARE @cols AS NVARCHAR(MAX)

DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT EntryDate INTO #Dates265
FROM Probale
WHERE (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate

SELECT @cols = @cols + ',' + QUOTENAME( CONVERT(varchar(20),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates265) TORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')

DECLARE @qry NVARCHAR(4000) =
N'SELECT *
FROM (SELECT ItemMasterFile.Descriptionitem,Probale.prdqty,
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale Inner Join ItemMasterFile on Probale.Codeitem=ItemMasterFile.Codeitem ``)prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat'EXEC(@qry);`

2

If you do a select @qry what does the query you're trying to execute actually look like?

1 Like
3

i am using this query for searching between two date
SELECT DISTINCT EntryDate INTO #Dates265
FROM Probale
WHERE (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate

4

That was not the question. What is the query that is stored in the @qry variable? Instead of doing exec(@qry) do a select @qry and post that value (the actual query you are trying to execute dynamically).

1 Like
5

thanks