Post
Ask Question
Ramco Ramco

Ramco Ramco

  • 419
  • 3.4k
  • 528.3k

Pivot in Sql

Apr 26 2023 4:35 AM

Hi

In below Stored Procedure i want if 5 Students have passed as parameter & there r only 3 records in Database then it should show also remaining Students in Column with Status as '-'

ALTER PROC [dbo].[USP_SessionBookPlanningRecommendation] --'0','20,21,22'
(
@BookID VARCHAR(max),
@StudentID VARCHAR(max)
)
AS
BEGIN
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],[AmazonURL],[Core RPL],[Lexile],[Session Plan Status],[Negative List],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[ComboBookName],[AmazonURL],[Core RPL],[Lexile],[Session Plan Status],[Negative List],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) 
END

GO

Thanks


Answers (1)