Home > database >  Generate tables from values of one column
Generate tables from values of one column

Time:08-02

New to SQL , I have a table called DataSetFact with a column called data

data
SOCIETE;SSY;Societe SSY
SOCIETE;XXX ;Societe XXX
ETABLISSEMENT;SITE_AV;Aveyron;AAT;SITE_AV
DEPOT;FRCAN ;ENTREPOT FOURNISSEUR FRCAN
GRP;APPMESURE;SUIVI APPAREILS DE MESURE
ARTICLE;59000013;REFERENCE Jumbo;MATPREMFAB;Article
ARTICLE;59000015;REFERENCE Jumbo222;MATPREMFAB222;Article

I want from the column data create 5 tables & Columns base on ; seperator

Table names are the first value SOCIETE , ETABLISSEMENT , DEPOT , GRP , ARTICLE

For example for table SOCIETE two columns would be created with values (SSY , Societe SSY)

CodePudding user response:

Create the tables as needed:

create table societe (ssy ..., societe_ssy ...);
create table etablissement (...);

Then you can use split_part extract the columns from each string in the source table:

insert into societe (ssy, societe_ssy)
select split_part(data, ';', 2),
       split_part(data, ';', 3)
from datasetfact
where data like 'SOCIETE%';

create table etablissement (...)

insert into etablissement (...)
select split_part(data, ';', 2),
       split_part(data, ';', 3),
       split_part(data, ';', 4)
from datasetfact
where data like 'ETABLISSEMENT%';
  • Related