declare @table table (name nvarchar(100),dr date, phone nvarchar(100))
insert @table
select 'Ivanov'as[fio], '10.01.1950'as[birth], '4956789'as[phone] union
select 'Ivanov', '20.11.1976', '123321' union
select 'Petrov', '23.12.1784', NULL union
select 'Sidorov', '26.12.1888', '7657875' union
select 'Sidorov', '12.04.1756', NULL
SELECT * FROM @table
-- для разделения однофамильцев по DR
DECLARE @fiodr TABLE (id int identity primary key, name nvarchar(100), dr date, unique(name,dr))
insert @fiodr SELECT /*DISTINCT*/ t.name,t.dr FROM @table t order by t.dr,t.name
CREATE TABLE fio (id int identity primary key, name nvarchar(100))
set identity_insert fio ON
insert fio(id,name) SELECT id,name FROM @fiodr
set identity_insert fio OFF
CREATE TABLE dr (id int identity primary key, idf int references fio(id), dr date)
insert dr SELECT DISTINCT f.id,t.dr FROM @table t join @fiodr f on f.name=t.name and f.dr=t.dr
CREATE TABLE ph (id int identity primary key, idf int references fio(id), num nvarchar(20))
insert ph SELECT DISTINCT f.id,t.phone FROM @table t join @fiodr f on f.name=t.name and f.dr=t.dr
SELECT * FROM fio
SELECT * FROM dr
SELECT * FROM ph
DROP TABLE dr, ph
DROP TABLE fio
|