How about this?
IF OBJECT_ID('dbo.RECORDS') IS NOT NULL
DROP TABLE dbo.RECORDS;
IF OBJECT_ID('dbo.NAMES') IS NOT NULL
DROP TABLE dbo.NAMES;
IF OBJECT_ID('dbo.DATES') IS NOT NULL
DROP TABLE dbo.DATES;
IF OBJECT_ID('dbo.PARENTS') IS NOT NULL
DROP TABLE dbo.PARENTS;
IF OBJECT_ID('dbo.BAPTISM') IS NOT NULL
DROP TABLE dbo.BAPTISM;
--Sample data in tables are, as follows
CREATE TABLE dbo.RECORDS
(
ID int,
book int,
page int,
number int
);
insert into dbo.RECORDS values ( 1, 1, 1, 1 );
insert into dbo.RECORDS values ( 2, 1, 1, 2 );
insert into dbo.RECORDS values ( 3, 1, 1, 3 );
insert into dbo.RECORDS values ( 4, 1, 1, 4 );
insert into dbo.RECORDS values ( 5, 1, 1, 5 );
insert into dbo.RECORDS values ( 6, 1, 1, 6 );
insert into dbo.RECORDS values ( 7, 1, 1, 7 );
insert into dbo.RECORDS values ( 8, 1, 1, 8 );
insert into dbo.RECORDS values ( 9, 1, 1, 9 );
insert into dbo.RECORDS values ( 10, 1, 1, 10 );
/*
TABLE RECORDS
ID book page number
1 1 1 1
1 1 1 2
1 1 1 3
etc. (I have ten records per each page)
*/
CREATE TABLE dbo.NAMES
(
ID int,
person_name varchar(50),
person_sex char(6),
priest_name varchar(50),
priest_title varchar(30),
godfather_name varchar(50)
);
insert into dbo.NAMES values ( 1, 'Lara', 'female', 'Alexandar Pesic', 'priest', 'Goran Jevtic' );
insert into dbo.NAMES values ( 2, 'Goran', 'male', 'Dusan Kovac', 'archpriest', 'Milena Taylor' );
/*
TABLE NAMES
ID person_name person_sex priest_name priest_title godfather_name
1 Lara female priest Alexandar Pesic priest Goran Jevtic
2 Goran male Dusan Kovac archpriest Milena Taylor
etc.
*/
CREATE TABLE dbo.DATES
(
ID int,
birth_date varchar(30),
baptism_date varchar(30)
);
insert into dbo.DATES VALUES ( 1, '1950., July 13.', '1952., November 14.' );
/*
TABLE DATES
ID birth_date baptism_date
1 13.07.1950 (in format YYYY., MMMM DD. e.t. 1950., July 13.) 14.11.1952 (in format 1952., November 14.)
etc.
*/
CREATE TABLE dbo.PARENTS
(
ID int,
father_first_name varchar(30),
father_last_name varchar(30),
father_nationality varchar(30),
father_faith varchar(30),
mother_name varchar(30),
mother_birthlast_name varchar(30)
);
insert into dbo.PARENTS values ( 1, 'Goran', 'Pesic', 'Serbian', 'Orthodox', 'Bosa', 'Delic' );
/*
TABLE PARENTS
ID father_first_name father_last_name father_nationality father_faith mother_name mother_birthlast_name...
1 Goran Pesic Serbian Orthodox Bosa Delic
etc.
My Result data should be, as excepted in application:
TABLE BAPTISM
ID book page number birth_date baptism_date person_name parent priest godfather
1 1 1 1 1950., July 13. 1952., November 14. Lara, female Goran Pesic and Bosa b. Delic, Serbian Orthodox...
I started my app for research over data. The reason for denormalizing is that I received application for printing our data in unique paper form, and that app use only one table, with all the data in it.
*/
CREATE TABLE dbo.BAPTISM
(
ID int,
book int,
page int,
number int,
birth_date varchar(30),
baptism_date varchar(30),
person_name varchar(100),
parent varchar(100),
priest varchar(100),
godfather varchar(100),
);
insert into dbo.BAPTISM
( ID, book, page, number, birth_date, baptism_date, person_name, parent, priest, godfather )
select
REC.ID,
REC.book,
REC.page,
REC.number,
DAT.birth_date,
DAT.baptism_date,
person_name = PAR.father_first_name + '' + PAR.father_last_name,
parent = 'father',
priest = NAM.priest_name,
godfather = NAM.godfather_name
from
dbo.RECORDS REC
INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID
INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID
INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;
select * from dbo.BAPTISM;
I'm not sure where the 'people' column comes from...
If you prefer, you can create the BAPTISM table without declaring it like this:
select
REC.ID,
REC.book,
REC.page,
REC.number,
DAT.birth_date,
DAT.baptism_date,
person_name = PAR.father_first_name + '' + PAR.father_last_name,
parent = 'father',
priest = NAM.priest_name,
godfather = NAM.godfather_name
into
dbo.BAPTISM
from
dbo.RECORDS REC
INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID
INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID
INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;