CODEWITHSUNDEEP
sql
Rafee SyEd
Rafee SyEd

Reputation: 27

How to denormalized data in SQL query

I have a table that has Clinic Names and Doctor Names. one clinic can have many doctors. I need to split this data into two tables. one with clinic info and the other with Doctor info

trying to do this in a SQL query

Table CLINIC_DOC:

ID  ClinicName  Doctor
------------------------
1   xyz         Dr Joe
2   xyz         Dr Bob
3   abc         Dr Mary
4   abc         Dr John

I want to split the data into the following tables like this:

Table ClinicsData: 

ClinicID    ClinicName
----------------------
1           xyz
2           abc

Table DoctorData: 

DocId   ClinicID    Doctor
--------------------------
1       1           Dr Joe
2       1           Dr Bob
3       2           Dr Mary
4       2           Dr John

Upvotes: 0

Views: 154

Answers (2)

digital.aaron
digital.aaron

Reputation: 5707

First, you'll probably want to create the tables you're going to populate. Here's my best guess at dataypes:

CREATE TABLE ClinicsData
(
    ClinicID INT IDENTITY(1,1),
    ClinicName varchar(100)
)

CREATE TABLE DoctorData
(
    DocID INT IDENTITY(1,1),
    ClinicID INT,
    Doctor VARCHAR(100)
)

Notice that I've made ClinicsData.ClinicID an IDENTITY column. This will help us to populate DoctorData later.

Next, let's populate ClinicsData with all the distinct clinic names.

INSERT INTO ClinicsData
(
    ClinicName
)
SELECT DISTINCT 
    ClinicName 
FROM CLINIC_DOC;

Now, we can utilize ClinicsData to populate DoctorData, using an INNER JOIN.

INSERT INTO DoctorData
(
    ClinicID
    ,Doctor
)
SELECT DISTINCT 
    cd.ClinicID,
    c_d.Doctor
FROM CLINIC_DOC c_d
INNER JOIN ClinicsData cd ON cd.ClinicName = c_d.ClinicName

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Assuming that the ID columns (ClinicID and DocID) are automatically generated and that the clinic names are unique (i.e there are no two clinics with the same name in the portion of the real world your data represents) you can try:

INSERT INTO clinicsdata
            (clinicname)
            SELECT DISTINCT
                   cd.clinicname
                   FROM clinic_doc cd;

INSERT INTO doctordata
            (clinicid,
             doctor)
            SELECT c.clinicid,
                   cd.doctor
                   FROM clinic_doc cd
                        INNER JOIN clinicsdata c
                                   ON c.clinicname = cd.clinicname;

Upvotes: 1

Related Questions