Pivot Table in SQL

Question

Hi please view the sample data: EmpCode Att_Date Duration_HR Duration_Mn Remarks NoOfLeaves 750 10/01/2012 9 25 Null 0 751 10/01/2012 8 50 Late 0 752 10/01/2012 9 8 A 0 750 10/02/2012 10 2 Null 0 752 10/02/2012 8 0 Late 0 753 10/02/2012 Null Null Null 1 I want to have this data in following 10/01/2012 10/02/2013 750 750 9:25 10:02 Null Null 0 0 751 752 8:50 8:00 Late Late 0 0 752 753 9:08 Null A Null 0 Null I am trying to go with Pivot but not able to get the perfect output. I will get the date range and EmpCode at runtime(from user).

Jignesh Trivedi · Answer

hi Rajeev I think it is very difficult to get this view. but try following code create table #temp ( EmpCode int null Att_Date datetime null Duration_HR int null Duration_Mn int null Remarks varchar(50) COLLATE Latin1_General_CI_AS null NoOfLeaves int null ) --Drop table #temp Insert into #temp values(750'10/1/2012'925Null0) Insert into #temp values(751'10/1/2012'850'Late'0) Insert into #temp values(752'10/1/2012'98'A'0) Insert into #temp values(750'10/2/2012'102Null0) Insert into #temp values(752'10/2/2012'80'Late'0) Insert into #temp values(753'10/2/2012'NullNullNull1) --select * from #temp SELECT col2value FROM ( SELECT cast(EmpCode as varchar(50))as col1convert(varchar(10)Att_Date101) as col2 cast(cast(Duration_HR as varchar(20)) + ':' + cast(Duration_Mn as varchar(20)) as varchar(50)) as col3 cast(NoOfLeaves as varchar(50)) as col6 isnull(Remarks'') as col7 FROM #temp ) p UNPIVOT (value FOR col IN (col1 col3 col7 col6) ) AS unpvt drop table #temp above query give you row wise data. Are you want to show this data in any reporting tool? hope this will help you.

EmpCode	Att_Date	Duration_HR	Duration_Mn	Remarks	NoOfLeaves
750	10/01/2012	9	25	Null	0
751	10/01/2012	8	50	Late	0
752	10/01/2012	9	8	A	0
750	10/02/2012	10	2	Null	0
752	10/02/2012	8	0	Late	0
753	10/02/2012	Null	Null	Null	1

10/01/2012	10/02/2013
750	750
9:25	10:02
Null	Null
0	0
751	752
8:50	8:00
Late	Late
0	0
752	753
9:08	Null
A	Null
0	Null