Insert pivot data into temp table

Question

Hi

I have below Pivot & want to insert data into temp table

declare @cols as nvarchar(max)='';
declare @query as nvarchar(max)='';
select @cols = @cols + QUOTENAME(StudentName) + ',' from (Select distinct StudentName from View_SessionBookPlanningRecommendation where StudentID in (select [value] from string_split(@StudentID,','))) as tmp
select @cols = substring(@cols,0,len(@cols))
set @query = 'select [BookId] [Book ID],BookTitle [Book],[ComboBookName],[Core RPL],[Lexile],[NumberOfPages],[Session Plan Status],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[ComboBookName],[Core RPL],[Lexile],[NumberOfPages],[Session Plan Status],Status,[ClosingStock],StudentName from View_SessionBookPlanningRecommendation where (('''+@BookID+'''=''0'' OR [BookId] IN('+ cast(@BookID as varchar(Max))+')))) x pivot
( Max(Status) for StudentName in (' + @cols + ')) piv order by BookTitle';
print @query
execute (@query)

Thanks

Naimish Makwana · Answer

Try below code: DECLARE @cols AS nvarchar(max)=''; DECLARE @query AS nvarchar(max)=''; SELECT @cols = @cols + QUOTENAME(StudentName) + ',' FROM ( SELECT DISTINCT StudentName FROM View_SessionBookPlanningRecommendation WHERE StudentID IN (SELECT [value] FROM string_split(@StudentID,','))) AS tmp; SELECT @cols = SUBSTRING(@cols,0,LEN(@cols)); SET @query = 'SELECT [BookId] [Book ID], BookTitle [Book], [ComboBookName], [Core RPL], [Lexile], [NumberOfPages], [Session Plan Status], [ClosingStock], ' + @cols + ' INTO #temp FROM ( SELECT [BookID], BookTitle, [ComboBookName], [Core RPL], [Lexile], [NumberOfPages], [Session Plan Status], Status, [ClosingStock], StudentName FROM View_SessionBookPlanningRecommendation WHERE ('''+@BookID+'''=''0'' OR [BookId] IN('+ CAST(@BookID AS varchar(Max))+'))) x PIVOT(Max(Status) FOR StudentName IN (' + @cols + ')) piv ORDER BY BookTitle'; PRINT @query; EXECUTE (@query); In this modified code, I added the INTO #temp clause to the SELECT statement of your dynamic SQL query. This clause will create a temporary table named #temp and insert the result set of your query into it. Note that the #temp table will only exist for the duration of your session, and will be automatically dropped when your session ends. If you need to access the data in the #temp table from another session or after your session has ended, you may want to use a global temporary table ( ##temp ) or a permanent table instead.

Tuhin Paul · Answer

Part 2 See the example query : DECLARE @cols AS nvarchar(max)=''; DECLARE @query AS nvarchar(max)=''; SELECT @cols = @cols + QUOTENAME(StudentName) + ',' FROM ( SELECT DISTINCT StudentName FROM View_SessionBookPlanningRecommendation WHERE StudentID IN (SELECT [value] FROM string_split(@StudentID,','))) AS tmp; SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)); SET @query = ' SELECT [BookId] [Book ID], BookTitle [Book], [ComboBookName], [Core RPL], [Lexile], [NumberOfPages], [Session Plan Status], [ClosingStock], ' + @cols + ' INTO #temp FROM ( SELECT [BookID], BookTitle, [ComboBookName], [Core RPL], [Lexile], [NumberOfPages], [Session Plan Status], Status, [ClosingStock], StudentName FROM View_SessionBookPlanningRecommendation WHERE ('''+@BookID+'''=''0'' OR [BookId] IN ('+ CAST(@BookID AS varchar(Max)) + '))) x PIVOT (MAX(Status) FOR StudentName IN (' + @cols + ')) piv ORDER BY BookTitle'; PRINT @query; EXECUTE (@query);

Tuhin Paul · Answer

Part 1: The issue is may be with the dynamic SQL query being constructed. Check the @cols variable is constructed correctly and contains the list of distinct StudentName values as expected. Also verify that the SELECT statement within the @query variable is retrieving the data in the correct format before the pivot operation is applied. You can try executing just that part of the query to see if the results are as expected. Check that the PIVOT clause is being constructed correctly, and that the FOR clause contains the correct column name (StudentName) and the list of column values generated by the @cols variable.

Ramco Ramco · Answer

Hi Naimish In same procedure i want to use #temp in Cursors . Is it possible or not When i write it gives Invalid Object name print @query execute (@query) select * from #temp Thanks

Ramco Ramco · Answer

HiNaimish Data is not showing in Pivot format Thanks