April 14, 2015 at 10:51 am
And to further my point, the cross tab approach would work for a whole slew of employees in one pass. The insert and multiple update approach you would have to put that in a loop/cursor to do the same thing. No offense mate but your approach would not work in one of my systems. It would be replaced with a set based approach that is scalable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2015 at 11:29 am
Sean Lange (4/14/2015)
And to further my point, the cross tab approach would work for a whole slew of employees in one pass. The insert and multiple update approach you would have to put that in a loop/cursor to do the same thing. No offense mate but your approach would not work in one of my systems. It would be replaced with a set based approach that is scalable.
I do note that I'm doing the insert in a separate step, and I missed that he's doing it as an insert in his requirements. Its cool if its not practical.
Here's both solutions, they'll work for the three columns but its true they're updates and not inserts. I'm just used to the fact that Employee tables usually exist already.
create table EmployeeCountry
(
EmployeeID int,
CountryID int
)
create table Employee
(
EmployeeID int,
Country1ID int,
Country2ID int,
Country3ID int
);
truncate table Employee;
truncate table EmployeeCountry;
insert into EmployeeCountry select 10001,20001;
insert into EmployeeCountry select 10001,20002;
insert into EmployeeCountry select 10001,20003;
insert into EmployeeCountry select 10002,20001;
insert into EmployeeCountry select 10002,20004;
insert into EmployeeCountry select 10002,20005;
insert into EmployeeCountry select 10003,20005;
insert into EmployeeCountry select 10003,20006;
insert into Employee select 10001, null, null, null;
insert into Employee select 10002, null, null, null;
insert into Employee select 10003, null, null, null;
-- i think Sean assumes the inserts into employee are part of the solution, whereas
-- i'm assuming the employee table already exists.
-- I do agree with Sean that inserts would be simpler.
select * from EmployeeCountry;
select * from Employee;
UPDATE Employee SET Country1Id = (SELECT MIN (CountryId) FROM EmployeeCountry EC WHERE EC.EmployeeID = Employee.EmployeeID)
UPDATE Employee SET Country2Id = (SELECT MIN (CountryId) FROM EmployeeCountry EC WHERE EC.EmployeeID = Employee.EmployeeID
AND CountryID <> Country1Id)
UPDATE Employee SET Country3Id = (SELECT MIN (CountryId) FROM EmployeeCountry EC WHERE EC.EmployeeID = Employee.EmployeeID
AND CountryID <> Country1Id AND CountryID <> Country2Id)
select * from Employee
select * from EmployeeCountry
-- now lets try a single update with rownumber!
truncate table Employee
truncate table EmployeeCountry
select * from Employee
select * from EmployeeCountry
insert into EmployeeCountry select 10001,20001;
insert into EmployeeCountry select 10001,20002;
insert into EmployeeCountry select 10001,20003;
insert into EmployeeCountry select 10002,20001;
insert into EmployeeCountry select 10002,20004;
insert into EmployeeCountry select 10002,20005;
insert into EmployeeCountry select 10003,20005;
insert into EmployeeCountry select 10003,20006;
insert into Employee select 10001, null, null, null;
insert into Employee select 10002, null, null, null;
insert into Employee select 10003, null, null, null;
-- the above is not part of the solution, its setting up the tables for updates.
With EmployeeCountrySrc as
(
Select EmployeeId Eid, CountryId, ROW_NUMBER () OVER ( PARTITION BY EmployeeId ORDER BY CountryId ) RowNum
From EmployeeCountry
)
update Employee set Country1Id = (SELECT CountryId From EmployeeCountrySrc Where EmployeeId = Eid AND RowNum = 1),
Country2Id = (SELECT CountryId From EmployeeCountrySrc Where EmployeeId = Eid AND RowNum = 2),
Country3Id = (SELECT CountryId From EmployeeCountrySrc Where EmployeeId = Eid AND RowNum = 3);
select * from Employee
select * from EmployeeCountry
April 14, 2015 at 11:53 am
Here's my insert version for completeness!
create table EmployeeCountry
(
EmployeeID int,
CountryID int
)
create table Employee
(
EmployeeID int,
Country1ID int,
Country2ID int,
Country3ID int
);
insert into EmployeeCountry select 10001,20001;
insert into EmployeeCountry select 10001,20002;
insert into EmployeeCountry select 10001,20003;
insert into EmployeeCountry select 10002,20001;
insert into EmployeeCountry select 10002,20004;
insert into EmployeeCountry select 10002,20005;
insert into EmployeeCountry select 10003,20005;
insert into EmployeeCountry select 10003,20006;
select * from EmployeeCountry;
select * from Employee;
With EmployeeCountrySrc as
(
Select EmployeeId Eid, CountryId, ROW_NUMBER () OVER ( PARTITION BY EmployeeId ORDER BY CountryId ) RowNum
From EmployeeCountry
)
INSERT into Employee (EmployeeId, Country1Id, Country2Id, Country3Id)
Select Eid, (SELECT CountryId From EmployeeCountrySrc e2 Where e2.Eid = EmployeeCountrySrc.Eid AND RowNum = 1),
(SELECT CountryId From EmployeeCountrySrc e2 Where e2.Eid = EmployeeCountrySrc.Eid AND RowNum = 2),
(SELECT CountryId From EmployeeCountrySrc e2 Where e2.Eid = EmployeeCountrySrc.Eid AND RowNum = 3)
From EmployeeCountrySrc Where RowNum = 1;
select * from Employee
select * from EmployeeCountry
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply