I am getting output for a report in SQL as mentioned below :-
create table ReportCustom (RoleID int, Supervisor varchar(250),UserECode int, UserName varchar(250),TimeSpent float,Activityname varchar(250))
go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0,'Process Training%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0.25,'L & D Training%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',3.12,'System Downtime%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',100,'Grand Total%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.22,'System Downtime%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.32,'Process Support%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.36,'Process Training%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',100,'Grand Total%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0,'L & D Training%')
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0.1,'System Downtime%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',0.05,'Non Production%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',100,'Grand Total%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21697,'Maria Elizabeth David',5.48,'L & D Training%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',4.48,'Process Support%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',93.61,'Production%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',100,'Grand Total%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.34,'System Downtime%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.6,'Process Training%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',1.17,'L & D Training%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',2.46,'Non Production%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',95.76,'Production%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',100,'Grand Total%')
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21645,'Sujatha Basa',0.71,'System Downtime%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',100,'Grand Total%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',1.508125,'L & D Training%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',100,'Grand Total%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',0.512857142857143,'L & D Training%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',46.2442857142857,'Production%')
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',6.8525,'System Downtime%')
Go
insert into ReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',100,'Grand Total%')
Go
insert into ReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',1.01049107142857,'L & D Training%')
but I need the output as mentioned below in OutPutReportCustomTable:-
create table OutPutReportCustom (RoleID int, Supervisor varchar(250),UserECode int, UserName varchar(250),TimeSpent float,Activityname varchar(250))
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0,'Process Training%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0.25,'L & D Training%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',3.12,'System Downtime%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',100,'Grand Total%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.22,'System Downtime%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.32,'Process Support%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.36,'Process Training%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',100,'Grand Total%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0,'L & D Training%')
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0.1,'System Downtime%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',100,'Grand Total%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',0.512857142857143,'L & D Training%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',46.2442857142857,'Production%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',6.8525,'System Downtime%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',0.05,'Non Production%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',100,'Grand Total%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21697,'Maria Elizabeth David',5.48,'L & D Training%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',4.48,'Process Support%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',93.61,'Production%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',100,'Grand Total%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.34,'System Downtime%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.6,'Process Training%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',1.17,'L & D Training%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',2.46,'Non Production%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',95.76,'Production%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',100,'Grand Total%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21645,'Sujatha Basa',0.71,'System Downtime%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',100,'Grand Total%')
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',1.508125,'L & D Training%')
Go
insert into OutPutReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',100,'Grand Total%')
Go
insert into OutPutReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',1.01049107142857,'L & D Training%')
The data in ReportCustom table is my input and i want the order of data as OutPutReportCustom table.In the ReportCustom table you can see that 6 distinct UserECode (80120,21697,21307,21413,21510,21645) with RoleID=1 is reporting to 'Subba Rayudu Yadavalli'.'Subba Rayadu' details are also in the same table. Now i want all the 6 Users details First and then after 'Subba Rayudu' details whose userecode is 20050 and roleid=2. Then again we we will take other User details and after that their Supervisor Details and so on till we complete all the RoleID=1 Users details and their Immediate Supervisor Details. So the OutPut will contain a series of RoleID=1 preceeding with RoleID=2 . At last we will display remaining RoleID if exists i.e RoleID=3 or 4 or 5 if any exists in sequential manner..