Insert pivot data into temp table

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

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

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.

HiNaimish

  Data is not showing in Pivot format

Thanks

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.