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

Answer

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;