SQL Query for interchanging rows position as per some condition

Dharmenrda Kumar Singh asked:

I am getting output for a report in SQL as mentioned below :-

SQL

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:-

SQL

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..

Tags: SQL, SQL Server 2000, SQL Server 2005, SQL Server, SQL Server 2008, SQL Server 2008R2

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

Cancel

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.