SQL Query根据某些条件交换行位置 [英] SQL Query for interchanging rows position as per some condition
问题描述
我正在获取SQL中的报告输出，如下所述： -
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%')
但我需要输出如下所述OutPutReportCustomTable： -
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..
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..
推荐答案
In SQL you can’’t really determine the order that records will appear (physically) in the table. You can control how they are retrieved though for example you could use
In SQL you can''t really determine the order that records will appear (physically) in the table. You can control how they are retrieved though for example you could use
select * from OutputReportCustom order by UserECode
If it is really important to you that the records are retrieved in the order that you inserted them into the database then I would create an additional column (e.g. RowId) with an IDENTITY attribute and then use
If it is really important to you that the records are retrieved in the order that you inserted them into the database then I would create an additional column (e.g. RowId) with an IDENTITY attribute and then use
select * from OutputReportCustom order by RowId
For example
For example
create table ReportCustom (
RowId int IDENTITY(1,1),
RoleID int, Supervisor varchar(250), UserECode int,
UserName varchar(250),TimeSpent float, Activityname varchar(250))
and use your second set of sql to do the inserts.
NB when inserting into the table do not supply a value for the RowId column - sql will do that for you - i.e. don’’t make any changes to your sql above except for the create table line
and use your second set of sql to do the inserts.
NB when inserting into the table do not supply a value for the RowId column - sql will do that for you - i.e. don''t make any changes to your sql above except for the create table line
这篇关于SQL Query根据某些条件交换行位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
