Stefan Kendall Stefan Kendall -4 years ago 89
SQL Question

Generating the unique sets of attributes into a new table?

I currently need to make a table that holds the unique sets of attributes for another table. That is, I have a table that looks like this:

Table A : columns = col 1, col2, col3, col4, col5


And I need to produce this:

Table B : columns = columnName, value
col 1, col1attribute1
col 1, col1attribute2
col 1, col1attribute3
col 2, col2attribute1
col 3, col3attribute1
col 3, col3attribute2


Is there an easy way to do this with (PL)SQL alone? It seems simple enough if I do it programatically in Java, but ideally I'd like an sql-only solution if possible.

Answer Source

The following seems to work:

CREATE TABLE TABLE_B (COLUMN_NAME, VALUE) AS
  SELECT DISTINCT 'COL_1', COL_1 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_2', COL_2 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_3', COL_3 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_4', COL_4 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_5', COL_5 FROM TABLE_A;

Share and enjoy.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download