April 9, 2017 at 8:30 am
Basically I have a table T1 with people, and another table T2 with their contact mechanisms say their phone numbers ( personal, work, etc...)
For every T1 row, I would like to map it to 3 rows in T2 so that in T3 which is the associative table, I will have T1.ID, T2.ID.
Is there a way to do this without looping? Thank you very much for your time and help.
declare @T1 table (
PartyId int,
Name varchar(50))
declare @T2 table (
ContactMechanismID int)
declare @T3 table (
Party int,
ContactMechanismID int)
Insert into @T1 (PartyID, Name)
Select 1,'Name1' UNION ALL
Select 2,'Name2' UNION ALL
Select 3,'Name3'
Insert into @T2 (ContactMechanismID)
Select 1 UNION ALL
Select 2 UNION ALL
Select 3 UNION ALL
Select 4 UNION ALL
Select 5 UNION ALL
Select 6 UNION ALL
Select 7 UNION ALL
Select 8 UNION ALL
Select 9
-- Results expected in T3
--Party ID ContactMechanism
--1 1
--1 2
--1 3
--2 4
--2 5
--2 6
--3 7
--3 8
--3 9
April 10, 2017 at 1:06 am
Quick suggestion
😎
USE TEEST;
GO
declare @T1 table (
PartyId int,
Name varchar(50))
declare @T2 table (
ContactMechanismID int)
declare @T3 table (
Party int,
ContactMechanismID int)
Insert into @T1 (PartyID, Name)
Select 1,'Name1' UNION ALL
Select 2,'Name2' UNION ALL
Select 3,'Name3'
Insert into @T2 (ContactMechanismID)
Select 1 UNION ALL
Select 2 UNION ALL
Select 3 UNION ALL
Select 4 UNION ALL
Select 5 UNION ALL
Select 6 UNION ALL
Select 7 UNION ALL
Select 8 UNION ALL
Select 9 ;
;WITH CONTACT_PARTY AS
(
SELECT
C.ContactMechanismID
,(FLOOR((C.ContactMechanismID - 1) / 3) + 1) AS PartyID
FROM @T2 C
)
SELECT
P.PartyId
,CP.ContactMechanismID
FROM @T1 P
INNER JOIN CONTACT_PARTY CP
ON P.PartyId = CP.PartyID;
Output
PartyId ContactMechanismID
1 1
1 2
1 3
2 4
2 5
2 6
3 7
3 8
3 9
April 10, 2017 at 5:22 am
I think we need more information on what you want to achieve here.
Eirikur's solution will give you exactly what you asked for. But is that what you really want?
I assume mechanisms have descriptions associated with the ID's - home phone, work phone, cell phone, pager (does anyone still have those?), email, work mail, etc.
The results would say that Name1 has a home, work and cell phone. Name2 has a pager, email and work email. Name3 has 3 other things. What does Name4 have?
This doesn't even consider the way you will be matching Name1 with Name1's own cell phone, and not somebody else's.
There has to be more to this data model to get any kind of predictable results.
April 10, 2017 at 7:20 am
gvoshol 73146 - Monday, April 10, 2017 5:22 AMI think we need more information on what you want to achieve here.Eirikur's solution will give you exactly what you asked for. But is that what you really want?
I assume mechanisms have descriptions associated with the ID's - home phone, work phone, cell phone, pager (does anyone still have those?), email, work mail, etc.
The results would say that Name1 has a home, work and cell phone. Name2 has a pager, email and work email. Name3 has 3 other things. What does Name4 have?
This doesn't even consider the way you will be matching Name1 with Name1's own cell phone, and not somebody else's.
There has to be more to this data model to get any kind of predictable results.
Also, his solution only works if all IDs from the second table are sequential with no gaps. That's why I didn't propose that solution. But I am brain-dead from a really bad travel weekend so cannot come up with a better solution. I do feel confident there is one though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 10, 2017 at 7:45 am
This method is slower but doesn't depend on sequential data, just on the PartyID being unique.
WITH cteParties AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY PartyID) pid
FROM @T1
),
cteContactsAssignment AS(
SELECT *, NTILE((SELECT COUNT(PartyID) Parties FROM @T1)) OVER( ORDER BY ContactMechanismID) pid
FROM @T2
)
SELECT p.PartyId,
c.ContactMechanismID
FROM cteContactsAssignment c
JOIN cteParties p ON c.pid = p.pid;
April 10, 2017 at 8:25 am
This does the same as Luis's fine code, slightly more efficiently though. Hesitant in posting such a solution as the requirements are somewhat unclear.
😎
USE TEEST;
GO
declare @T1 table (
PartyId int,
Name varchar(50))
declare @T2 table (
ContactMechanismID int)
declare @T3 table (
Party int,
ContactMechanismID int)
Insert into @T1 (PartyID, Name)
Select 1,'Name1' UNION ALL
Select 2,'Name2' UNION ALL
Select 3,'Name3'
Insert into @T2 (ContactMechanismID)
Select 1 UNION ALL
Select 2 UNION ALL
Select 3 UNION ALL
Select 4 UNION ALL
Select 5 UNION ALL
Select 6 UNION ALL
Select 7 UNION ALL
Select 8 UNION ALL
Select 9 ;
SELECT
C.ContactMechanismID
,((ROW_NUMBER() OVER
(
PARTITION BY @@VERSION
ORDER BY C.ContactMechanismID
) - 1) / 3) + 1 AS PartyID
FROM @T2 C;
The output is identical to the one before.
April 10, 2017 at 1:10 pm
Many thanks to both Eirikur and Luis for providing a solution though the statement is unclear . That works for what I am planning to do. It is good to know there is great help in this forum!
Just some more information for the inquisitive minds, the script is a data migration script. PartyID is unique. And I do happen to be able to grab as many identifiers sequentially and reserve them for a script purpose.
We are moving from a specific relational database to a universal relational database.
Objects that can perform actions in our domain are organizations and people all of which are parties. Parties can be contacted via mechanisms.
So if I have an Org row with Sales Support phone number and Help Desk Support phone Number, an OrgAddress row, a group email address for the Org in the specific database,
I will have to get 4 mechanism IDs to specify their mechanism types, then go the specified subtypes ( Postal Address, TeleComm Number, Electronic Address) to store the finer information.
I do have 2 options: (1) Ensure each party gets assigned 4 sequential mechanisms and work from there
(2) Make 4 blocks of code (each block takes all orgs and one specific mechanisms linked via their row_number())
The objective is to make the execution time spent on data migration as short as possible for the night of the release so I am giving it a try.
Regards
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply