Pivot table query is executing but no data return ,

Akhter HUssain

7y808SQL

here is my pivot table query ,it is executing but no data return.

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 ) T

ORDER 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 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nirav Gandhi7y ago
Hi,

I would suggest instead of "PIVOT" create a dynamic query using while loop.

PIVOT only supports 255 columns and some time create performance issues.

For Example:
/**
1) create a Temp table and take column name from Dates tables
2) create a temp table for final output
3) Use While loop and Update columns

**/

While (@intCnt<=(select Count(*) from #tmep))
Begin

Exec("Alter table #temp1 add Column '+@columnname+' varchar(100))

exec ('Update #temp1 set '+@columnName+'= value from #temp1
inner join #temp on t.date=T1.date)

set @intcnt=@intcnt+1

end
Nitin Sontakke7y ago
You may wish to print the dynamic query that is generated and execute that in SQL Server Management Studio and then check why it is not returning any data.