Hi
I want to use pivot Data for further processing with cursors.
IF OBJECT_ID('tempdb..#finalTable') IS NOT NULL DROP TABLE #finalTable
CREATE TABLE #finalTable ([ID] INT, [BookID] NVARCHAR(100), [BookTitle] VARCHAR(100), [AmazonURL] NVARCHAR(max), [NumberOfPages] VARCHAR(20), [Lexile] NVARCHAR(100),
[CoreRPL] NVARCHAR(100),[ComboBookName] NVARCHAR(100), [Negative List] NVARCHAR(100), [SessionPlanStatus] NVARCHAR(100), [StudentID] NVARCHAR(100), [Name] NVARCHAR(100), [StudentName] NVARCHAR(100), [Status] NVARCHAR(100),
[EntryType] NVARCHAR(10), [ClosingStock] INT)
;with CTE as
(
SELECT SP.[ID]
,SP.[BookID]
,B.[BookTitle]
,B.[AmazonURL]
,B.[NumberOfPages]
,B.[Lexile]
,B.BookRPL [CoreRPL]
,(select [CombosBookName] from [dbo].[ComboBookMaster] where [CombosBookID] = B.CombosBookID) [ComboBookName]
,CASE WHEN B.[BookAvailableForSession] = 0 THEN 'N' ELSE '' END AS [Negative List]
,(Select TOP 1 V.[CurrentStatus] FROM [View_SessionDetails] V WHERE V.ID=B.ID) [SessionPlanStatus]
,SP.[StudentID]
,ST.[Name]
,ST.[Name]+' ('+ST.[MobileNo]+')' [StudentName]
,SP.[Status]
,'H' AS [EntryType]
,(select closingstock from [View_WareHouseStockSummary] where BookId = SP.[BookID]) as [ClosingStock]
FROM [SessionBookPlanningHistoricalData] SP
JOIN [BookDetails] B
ON SP.BookID=B.[ID]
JOIN [StudentDetails] ST
ON SP.[StudentID]=ST.[LoginCode]
WHERE SP.[Deleted]=0 and SP.[StudentID] in (select [value] from string_split(@StudentID,','))
Union All
SELECT T0.[ID]
,T0.[BookID]
,T0.[BookTitle]
,(Select T1.[AmazonURL] from [BookDetails] T1 where T1.ID = T0.BookID) [AmazonURL]
,(Select T1.[NumberOfPages] from [BookDetails] T1 where T1.ID = T0.BookID) [NumberOfPages]
,(Select T1.[Lexile] from [BookDetails] T1 where T1.ID = T0.BookID) [Lexile]
,T0.[CoreRPL] as [CoreRPL]
,(select T1.[CombosBookName] from [dbo].[ComboBookMaster] T1 where T1.[CombosBookID] = (select T2.CombosBookID from [BookDetails] T2 where T2.Id = T0.BookID)) [ComboBookName]
,T0.[NegativeList] AS [Negative List]
,T0.[SessionPlanStatus] AS [SessionPlanStatus]
,T0.[StudentID]
,T0.[Name]
,T0.[StudentName]
,T0.[Status]
,T0.[EntryType]
,T0.[ClosingStock]
FROM [SessionBookPlanningDetailed] T0
WHERE T0.[HistoricalDataCount]=0 and T0.[StudentID] in (select [value] from string_split(@StudentID,','))
Union All
SELECT 0
,B.[ID]
,B.[BookTitle]
,B.[AmazonURL]
,B.[NumberOfPages]
,B.[Lexile]
,B.BookRPL [CoreRPL]
,(select [CombosBookName] from [dbo].[ComboBookMaster] where [CombosBookID] = B.CombosBookID) [ComboBookName]
,CASE WHEN B.[BookAvailableForSession] = 0 THEN 'N' ELSE '' END AS [Negative List]
,(Select TOP 1 V.[CurrentStatus] FROM [View_SessionDetails] V WHERE V.ID=B.ID) [Session Plan Status]
,''
,''
,''
,''
,'H' AS [EntryType]
,(select closingstock from [View_WareHouseStockSummary] where BookId = B.[ID]) as [ClosingStock]
FROM [BookDetails] B
where B.BookRPL = @Rpl and B.ID not in (Select BookId from View_SessionBookPlanning where StudentID not in (select [value] from string_split(@StudentID,',')) and BookRPL = @Rpl)
)
INSERT INTO #finalTable
select * from CTE
--select * from #finalTable
select @cols = @cols + QUOTENAME(StudentName) + ',' from (Select distinct StudentName from #finalTable 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],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],[ClosingStock], ' + @cols + '
INTO #temp from (select [BookID] ,BookTitle,[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],Status,[ClosingStock],StudentName from #finalTable
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