Splitting up data from one table to another-CodePudding

Currently I have a table like shown in T1 that I'd like to split up into individual Person table like T2 and a PersonClientLink table like T3. Each relationship is stored as its own unique ID in T1. This makes duplicates of the same person and their clients which is making this difficult. I need to group the Persons from T1 into a single ID and basically turn it into T2 and create links for each relationship they have into T3. T3 Currently exists but is empty, I want to populate T3 based on the data in T1 first and then turn T1 into T2. Any advice would be appreciated :)

T1 - Current mess

ID	Client_Id	Name
1	5	Bob
2	6	Bob
3	7	Greg
4	8	Greg

T2 - Person

ID	Name
1	Bob
2	Greg

T3 - PersonClientLink

ID	Person_Id	Client_Id
1	1	5
2	1	6
3	2	7
4	2	8

I'm honestly clueless no idea where to begin with this...

CodePudding user response：

I would create the T2 and T3 tables and populate them from T1. I would then drop T1 afterwards. The following is done with postgresql, but it should be the same. A serial column in postgresql is the same as an identity column in sqlserver.

create table t2 (id serial primary key,
                 name varchar(32) not null);
create table t3 (id serial primary key, 
                 person_id integer not null, 
                 client_id integer not null);

Create a foreign key from t3.person_id to t2.id
Populate t2 and t3:

insert into t2 (name) (select distinct name from t1);
insert into t3 (person_id,client_id) (
    select t2.id,t1.client_id from t1,t2 where t2.name = t1.name);

Convert the join of the tables to ANSI style if sqlserver requires that.
The initial data (same as yours) and the results:

select * from t1;
 id | client_id | name
---- ----------- ------
  1 |         5 | bob
  2 |         6 | bob
  3 |         7 | greg
  4 |         6 | greg
(4 rows)

select * from t2;
 id | name
---- ------
  1 | bob
  2 | greg
(2 rows)

select * from t3;
 id | person_id | client_id
---- ----------- -----------
  1 |         1 |         6
  2 |         1 |         5
  3 |         2 |         6
  4 |         2 |         7
(4 rows)

I hope this helps.