CODEWITHSUNDEEP
sql
Calgar99
Calgar99

Reputation: 1688

Is there a way to dynamically add columns to table in SQL

I'm trying to create a master table containing information relevant to different countries. These Countries have many duplicate columns and many unique coulmns. Is there a way to dynamically add a column name to a Master table.

For example.

Table 1

Country Acc 1000 Acc 1010 Acc 1020 etc
UK           x        x         x
UK           x        x         x

Table 2

Country Acc 1000 Acc 1010 Acc 1030 etc
FR           x        x         x
FR           x        x         x

Master

Country Acc 1000 Acc 1010  Acc 1020    Acc 1030 etc
FR             x        x         0           x
FR             x        x         0           x   
UK             x        x         X           0
UK             x        x         X           0

Any guidance would be much appreciated.

Upvotes: 0

Views: 57

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 157136

You could better normalize this that trying to dynamically add columns to a table.

You could use a view using pivoting to transpose the results when you want to create a user-friendly view.

Option 1, stick with this plan:

  • Create a after insert, update, delete trigger and use execute immediate to alter the table;

Option 2, normalize:

  • Create a acc table;
  • Edit that one, you would normally do with tables;
  • Read up on pivot / unpivot in the Oracle documentation.

Upvotes: 1

Related Questions