Pivot Table

Question

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

Deepak Tewatia · Answer

Hi Ramco, Try to use the below query, This view will create separate columns for the name and number of days for each level, using the PIVOT function. The PIVOT function will take the Name and Days columns and create a new column for each level, using the LevelName column as the pivot column. The resulting view will have columns Level1_Name, Level1_Days, Level2_Name, Level2_Days, and so on, containing the name and number of days for each level. ALTER VIEW [dbo].[View_CPX_Pivot] AS SELECT [PR Number], [Total Days], [Level 1] as [Level1_Name], [Level1_Days], [Level 2] as [Level2_Name], [Level2_Days], [Level 3] as [Level3_Name], [Level3_Days], [Level 4] as [Level4_Name], [Level4_Days], [Level 5] as [Level5_Name], [Level5_Days], [Level 6] as [Level6_Name], [Level6_Days], [Level 7] as [Level7_Name], [Level7_Days], [Level 8] as [Level8_Name], [Level8_Days], [Level 9] as [Level9_Name], [Level9_Days], [Level 10] as [Level10_Name], [Level10_Days], [Level 11] as [Level11_Name], [Level11_Days], [Level 12] as [Level12_Name], [Level12_Days] 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] AS [Name], DATEDIFF(d, [RequestDate], [ApprovalDate]) as [Days], LevelName FROM [View_Capex_StepWiseApprovalDetail] L group by l.entrycode, l.levelname, l.RequestDate, l.ApprovalDate, EmpName ) t PIVOT( MIN( [Name] + ' - ' + CAST( [Days] as Varchar(50) ) + ( CASE WHEN [Days]