Ramco Ramco

Ramco Ramco

  • 471
  • 2.8k
  • 393.5k

Pivot Table

Nov 14 2022 7:53 AM

Hi

  I have below code & attached image also . I don't want to display Approver as separate column but i want to display LevelName as APprover+LevelName. 12 rows data be displayed in 1 row.

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]
      ,Min([EmpName]) + ' - ' + [LevelName]   as Approver
	  ,LevelName
      FROM [View_Capex_StepWiseApprovalDetail] L
	  where l.EntryCode = 209
	  group by l.entrycode,l.levelname,l.RequestDate,l.ApprovalDate
	  
) 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

Thanks


Answers (1)