SQL Query for interchanging rows position as per some condition

Question

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%')

Go

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

Can you provide the code that generates the SQL along with code for generating sample versions of your table? — Registered User, Apr 29 '13 at 07:04
Sure i will provide but for time being if you will try to save the code in HTML format mentioned in my Question in your machine , you can see my Input and Output table structure with Sample data. — Dharmendra Kumar Singh, Apr 29 '13 at 07:18
Updated my question and provides code that generates the SQL along with some sample data and also what output i am looking for in OutPutRecordCustom table. — Dharmendra Kumar Singh, Apr 29 '13 at 09:44
Note that there is no guaranty that records are returned in *any* particular order from an SQL query, unless you specify an order by clause. What did you try so far? — Stefan Steinegger, Apr 29 '13 at 11:03
I don't understand the question. How should it be ordered? I can't see any rule in what you call "the output". — Stefan Steinegger, Apr 29 '13 at 11:20
Can you please provide an example with easy to understand sample data, like "A", "B", "C", instead of long and strange and looking-all-the-same names (sorry...)? Maybe then I'll understand what you need. — Stefan Steinegger, Apr 29 '13 at 11:24
@StefanSteinegger he is trying to do something [like](http://stackoverflow.com/questions/8899230/sql-hierarchy-id-sorting-by-level) — WiiMaxx, Apr 29 '13 at 12:10
@StefanSteinegger, thanks for your cooperation and i had updated my question so that you can understand it more better. — Dharmendra Kumar Singh, Apr 29 '13 at 12:52
@Dharmendra Singh: I added another section to my answer about ordering. You can't rely on the order in which you insert data. — Stefan Steinegger, Apr 29 '13 at 14:35

Stefan Steinegger · Accepted Answer · 2013-04-29T20:40:51.420

I'm not sure if I understand your question correctly. It seems as you want to order in a hierarchical way. You can't solve this in a single SQL query. Starting with a supervisor (which one?) then continuing with its supervisor.

I wonder if you don't actually want the supervisor first, then all its workers.

You can't solve this in a single SQL query, unless there is a known maximum depth of the hierarchy. If not, you need to execute an SQL query for each level in the hierarchy (or even each branch). There may be SQL extensions which can handle hierarchies, but I don't know how this works.

3 levels of hierarchy (may be not all correct, but may help you anyway):

Select worker.* 
FROM OutPutReportCustom worker
  left outer join OutPutReportCustom supervisor 
      on worker.Supervisor = supervisor.userName
  left outer join OutPutReportCustom supersupervisor 
      on supervisor.Supervisor = supersupervisor.userName
order by
  worker.UserName,
  supervisor.UserName,
  supersupervisor.UserName

By the way: you should probably consider to normalize your data. (particularly: use foreign keys instead of names.)

Edit:

Note that there is no guaranty that records are returned in any particular order from an SQL query, unless you specify an order by clause. You can't rely on the order in which you insert data into the table. (Even though they may be returned in the same order in all your tests, this works only until the data gets fragmented, which can't be controlled).

The most common way is to order the data when reading. If the logic how data is ordered is in the part that is writing the data, consider adding another "ordering" column, which holds a numeric value. You can easily order by this when querying the data.

iCode · Answer 2 · 2013-04-29T07:11:23.733

0

In the query give it as

Select * from (your table) order by SupervisorName;

But you won't get in the order want(I mean you wont get Subba Rayudu first and then Avinash, then Rajani, etc.. It will be in soreted order.). But it will show rows with same Supervisor Name together.

edited Apr 29 '13 at 07:11

answered Apr 29 '13 at 07:04

iCode

8,892
21
57
91

I dont need that details. I am already getting the result as per you are providing but as per requirement - i need to change the place of roleid 2 record just after roleid=1 records with same Supervisor Name. – Dharmendra Kumar Singh Apr 29 '13 at 07:15
This query does what you want. Because it is ordering by supervisor name. The resultset value will be like that. Run this query and check your Web page. – iCode Apr 29 '13 at 07:26
Sorry @Shiju, It is not working because it is ordering by supervisor name and just after ordering by supervisor name i want the supervisor details get displayed and then again next Details order by supervisor name and once it is completed then the supervisor details and so on... – Dharmendra Kumar Singh Apr 29 '13 at 09:10
I don't get your requirements then. Sorry – iCode Apr 29 '13 at 09:19
Updated my question with SQL queries for both input and output so that you understand my requirement easily. – Dharmendra Kumar Singh Apr 29 '13 at 09:42

SQL Query for interchanging rows position as per some condition

2 Answers2