How can I get below answer based on given tables with data? I have done some steps but not able to get the answer

Transact-SQL
1

Tables:

TABLE1: PROGRAM

Prgm_name Company People
A X CO., Y CO. JOHN
B M CO., N CO., O CO. KRISH, BIKRAM, TOM

TABLE2: COMPANY

Company_name Program People
X CO. A JOHN
Y CO. A
M CO. B
N CO. B KRISH, TOM
O CO. B

TABLE3: PEOPLE

Full_name Company program
JOHN X CO. A
BIKRAM B
KRISH N CO. B
TOM N CO. B
LYNN N CO.

create table Program
(
Prgm_name varchar(225),
Company varchar(500),
People varchar(500)
)

create table Companies
(
Company_name varchar(255),
Program varchar(500),
People varchar(500)
)

create table People
(
Full_name varchar(225),
Company varchar(500),
program varchar(500)
)

insert into program values ('A', 'X CO., Y CO.', 'JOHN')
insert into program values ('B', 'M CO., N CO., O CO.', 'KRISH, BIKRAM, TOM')

insert into Companies values ('X CO.','A','JOHN')
insert into Companies values ('Y CO.','A','')
insert into Companies values ('M CO.','B','')
insert into Companies values ('N CO.','B','KRISH, TOM')
insert into Companies values ('O CO.','B','')

insert into People values ('JOHN','X CO.','A')
insert into People values ('BIKRAM','','B')
insert into People values ('KRISH','N CO.','B')
insert into People values ('TOM','N CO.','B')
insert into People values ('LYNN','N CO.','')

SELECT * FROM Program
SELECT * FROM Companies
SELECT * FROM people

EXPECTED RESULT: Based on the above 3 tables I want the normalized PROGRAM table like the below:

Prgm_name Company People
A X CO. JOHN
A Y CO.
B N CO. KRISH
B BIKRAM
B N CO. TOM
B M CO.
B O CO.

To get this result I tried to write some script but still not getting the answer. The script is as follows:

select * into #program from
(
select a.PRGM_NAME as PRGM_NAME_P, a.COMPANY AS COMPANY_P, a.PEOPLE AS PEOPLE_P, c.Full_name AS FULL_NAME_PP,c.Company AS COMPANY_PP,c.program AS PROGRAM_PP from
(
select distinct b.PRGM_NAME, x.COMPANY, Y.PEOPLE
from
(SELECT PRGM_NAME, COMPANY, PEOPLE FROM Program) b
cross apply(select trim(value) from string_split(b.COMPANY, ',')) x(COMPANY) CROSS APPLY (select trim(value) from string_split(b.PEOPLE, ',')) Y(PEOPLE)
)a
left join
people c
on a.Prgm_name = c.program AND A.COMPANY = C.Company
)d

SELECT
PRGM_NAME_P,COMPANY_P AS COMPANY_P_Y, PEOPLE_P, FULL_NAME_PP, COMPANY_PP,PROGRAM_PP
,CASE
WHEN COMPANY_P <> '' AND COMPANY_PP = '' THEN FULL_NAME_PP
WHEN COMPANY_P <> COMPANY_PP THEN ''
ELSE FULL_NAME_PP
END FULL_NAME_Y
, ROW_NUMBER () OVER(PARTITION BY PRGM_NAME_P, PEOPLE_P ORDER BY COMPANY_PP DESC ) AS RANK1
FROM #program

I am not able to do more than this. it would be a great help if anyone guide me.

2

The lack of normalization make this a mess. I am not quite sure what you want but the following works with the test data:

WITH ProgramCompanies
AS
(
	SELECT P.Prgm_name, LTRIM(X.[value]) AS Company
	FROM Program p
		CROSS APPLY STRING_SPLIT(P.Company,',') X
)
,ProgramPeople
AS
(
	SELECT P.Prgm_name, LTRIM(X.[value]) AS Person
	FROM Program p
		CROSS APPLY STRING_SPLIT(P.People,',') X
)
,NCompanies
AS
(
	SELECT Program, Company_name, LTRIM(X.[value]) AS Person
	FROM Companies C
		CROSS APPLY STRING_SPLIT(C.People,',') X
)
,CompanyPrograms
AS
(
	SELECT PC.Prgm_name, PC.Company, C.Person
	FROM ProgramCompanies PC
		JOIN NCompanies C
			ON PC.Prgm_name = C.Program
				AND PC.Company = C.Company_name
)
,PeoplePrograms
AS
(
	SELECT PP.Prgm_name, PP.Person
	FROM ProgramPeople PP
		JOIN People P
			ON PP.Prgm_name = P.Program
				AND PP.Person = P.Full_name
)
SELECT COALESCE(CP.Prgm_name, PP.Prgm_name) AS Prgm_name
	,COALESCE(CP.Company, '') AS Company
	,COALESCE(CP.Person, PP.Person) AS Person
FROM CompanyPrograms CP
	FULL JOIN PeoplePrograms PP
		ON CP.Prgm_name = PP.Prgm_name
			AND CP.Person = PP.Person;
1 Like
3

I'm not sure what you want to do, but even this simple query gives the result you want.

SELECT
	Program
	, Company_name
	, r.value AS People
FROM Companies
CROSS APPLY STRING_SPLIT(People, ',') as r
UNION ALL
SELECT Program, Company, Full_name FROM People WHERE ISNULL(Company, '') = ''

Of course, a correct result set does not mean that the query that generated it is the correct one in general!
:slight_smile:

1 Like
4

Thank you, Ifor for the brilliant answer. I have two more questions. 1. If I want to normalize these tables how can normalize these tables (steps)? 2. if I want some change in the tables data like below what kind of change needs to be done in the query. I tried some ways but did not work. the updated table data is as follows:
I have added just

PROGRAM TABLE:

Prgm_name Company People
A X CO., Y CO. JOHN
B M CO., N CO., O CO., X CO. KRISH, BIKRAM, TOM

COMPANY TABLE:

Company_name Program People
X CO. A,B JOHN, TOM
Y CO. A
M CO. B
N CO. B KRISH, TOM
O CO. B

PEOPLE TABLE:

Full_name Company program
JOHN X CO. A
BIKRAM B
KRISH N CO. B
TOM N CO., X CO. B
LYNN N CO.

EXPECTED RESULT:

name companies_involved Person
A X CO. JOHN
A Y CO.
B BIKRAM
B M CO.
B N CO. KRISH
B N CO. TOM
B O CO.
B X CO. TOM
5

There are plenty of examples. eg What is Database Normalization in SQL Server? (sqlshack.com)

You need to concentrate on first normal form.

2 Likes