0

Much like these questions:
MSSQL Create Temporary Table whose columns names are obtained from row values of another table

Create a table with column names derived from row values of another table

I need to do the same thing with Oracle, but i also need to fill this new table with data from another table which is organized in a particular way. An Example:

Table Users

|id|name |
----------
|1 |admin|
|2 |user |

Table user_data_cols

|id|field_name|field_description|
---------------------------------
|1 |age       |Your age         |
|2 |children  |Your children    |

Table user_data_rows

|id|user_id|col_id|field_value|
-------------------------------
|1 |1      |1     |32         |
|2 |1      |2     |1          |
|3 |2      |1     |19         |
|4 |2      |2     |0          |

What i want is to create, using only sql, a table like this:

|user_id|age|children|
----------------------
|1      |32 |1       |
|2      |19 |0       |

Starting from the data in the other tables (which might change with time so i'll need to create a new table if a new field is added) Is such a thing even possible? I feel this might be against a lot of good practices but it can't be helped...

Community
  • 1
  • 1
valepu
  • 3,136
  • 7
  • 36
  • 67
  • What is the logic to get the resulting data? Do you have a normal query just to get the data? And what do you mean by *"create a temporary table"*? Do you just mean some normal table but you will use temporarily? Because if you have a SQL, you can just do: `create table temp_table_name as select...` – sstan Sep 24 '15 at 14:24
  • i have a query to get the data, but the data will be in "vertical" form, as in row1=>user, field_name, field_value, row2=>user, field_name, field_value. For example i'd have ["admin", "age", 32],["admin", "children", 1],["user", "age", 19],["user", "children", 0]. What i want is this data in tabular form, with "field_name" values as column names and "field_value" values as rows data using only SQL – valepu Sep 24 '15 at 14:32
  • You're talking about pivoting. You can easily find information about how to do that. But be aware that to do pivoting, you'll have to know in advance which values of `field_name` you will want to transform into column names. – sstan Sep 24 '15 at 14:38
  • I know about pivoting and it's not what i'm looking for because as you said i need to know in advance the values used. – valepu Sep 24 '15 at 14:43
  • You need *"dynamic column pivoting"* (if such a term exists)? – sstan Sep 24 '15 at 14:44
  • Essentially, yes (i like new terms, if they become something concrete). Maybe it'd be possible with a stored procedure? – valepu Sep 24 '15 at 14:46
  • possible duplicate of [Dynamic pivot in oracle sql](http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) – sstan Sep 24 '15 at 14:49
  • About this requirement "using only sql", does this statement mean that you will not consider a pl/sql block as an answer? – Robert Dupuy Sep 24 '15 at 16:49
  • "Maybe it'd be possible with a stored procedure?" - yes, definitely. The issue I have with your original question is you restrict the answer to 'using only sql'. – Robert Dupuy Sep 24 '15 at 17:15
  • My bad here, misused terms. I meant to say if it was possible to do that without involving external code. Now i'm checking your suggestions – valepu Sep 25 '15 at 07:04
  • the Dynamic pivoting described in the article https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ did the trick, thanks! (it's in one of answers from the duplicate, though it's not the accepted answer) – valepu Sep 25 '15 at 07:54

1 Answers1

2

For comparison purposes here is the answer from your link:

Create a table with column names derived from row values of another table

SELECT
  CONCAT(
    'CREATE TABLE Table_2 (',
    GROUP_CONCAT(DISTINCT
      CONCAT(nameCol, ' VARCHAR(50)')
      SEPARATOR ','),
    ');')
FROM
  Table_1
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Now let's do the same in Oracle 11g:

DECLARE
  stmt varchar2(8000);
BEGIN
  SELECT 'CREATE TABLE Table_2 ('||
  (SELECT 
      LISTAGG(nameCol, ', ') WITHIN GROUP (ORDER BY nameCol) "cols"
  FROM 
  (SELECT DISTINCT nameCol||' VARCHAR2(50)' nameCol FROM table_1) table_x)||
  ')'
INTO stmt
FROM DUAL;
EXECUTE IMMEDIATE stmt;
END;
/

You asked if you can do this using 'using only sql', my answer uses a PL/SQL block.

You can fill such a table with data, using a similar strategy. As others have noted, you must know the columns at parse time, to get around that restriction, you can follow a strategy such as was done here:

Dynamic Pivot

Robert Dupuy
  • 857
  • 5
  • 10
  • I ended using this https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ which has been suggested in a post of the discussion you linked – valepu Sep 25 '15 at 07:58