Ramco Ramco

Ramco Ramco

  • 471
  • 2.8k
  • 393.3k

Pivot Table

Nov 14 2022 2:44 PM

Hi

  In below Pivot Data is displayed like below . I want Name to come separate , No of days separate .

ALTER VIEW [dbo].[View_CPX_Pivot] AS 
SELECT  * FROM   
(
    SELECT 
       (Min(L.[EntryCode])) AS [PR Number]
	  --,L.[RequestDate] [Entry Date]
	  --,L.[ApprovalDate] [Completion Date]
	  ,(select top 1 DATEDIFF(d,Min([RequestDate]),Max([ApprovalDate])) FROM [View_Capex_StepWiseApprovalDetail] where EntryCode=L.EntryCode) as [Total Days]
	  --,DATEDIFF(d,[RequestDate],[ApprovalDate]) as [DaysToApprove]
	  ,[EmpName]+' - '+CAST(DATEDIFF(d,[RequestDate],[ApprovalDate]) as Varchar(50)) + (CASE WHEN DATEDIFF(d,[RequestDate],[ApprovalDate]) <= 1 THEN ' Day' ELSE ' Days' END) AS [DaysToApprove]
	  ,LevelName
      FROM [View_Capex_StepWiseApprovalDetail] L
	  group by l.entrycode,l.levelname,l.RequestDate,l.ApprovalDate,EmpName
) t 
PIVOT(
    MIN([DaysToApprove]) 
    FOR [LevelName] IN ([Level 1], [Level 2], [Level 3], [Level 4], [Level 5], [Level 6], [Level 7], [Level 8], [Level 9], [Level 10], [Level 11], [Level 12])
) AS pivot_table1
GO

I want that it should be displayed like below

     20              100                Sudhir Kumar             30 Days            Davinder        1 Day & so on

Thanks


Answers (1)