Joining columns to rows.

Question

Here's a scenario with a product we purchased. THe product allows us to create custom fields, but these fields are stored as ROWS in a CUSTOM table.

I want to write a query that will connect to more than one custom field and fetch a single row.

Let me give you an example.

1) PERSON TABLE (ID int, NAME varchar2(30));
2) CUSTOMFIELDS TABLE(CFID int, CFTable varchar2(30), CFFieldName varchar2(30), CFFieldType varchar2(30));
3) CUSTOMFIELDVALUE TABLE(CFVID int, CFID int, CFFieldName varchar2(100), CFFieldValue varchar2(100));

My person table has one record

1) 1001 - Clark Kent

Let's say I create two custom fields for person called AGE and WEIGHT. In that case two records will get created in teh CUSTOMFIELDS table.

1) 100 - PERSON - AGE - INTEGER
2) 200 - PERSON - WEIGHT - INTEGER

Now the values for these custom fields will be stored in the CUSTOMFIELDVALUE table like this.

1) 100 - 100 - 1001 - 44
2) 101 - 200 - 1001 - 200 lbs

I want to write a select query that will fetch the record like this

PERSON, AGE , WEIGHT
Clark Kent, 44, 200 lbs

I am thinking how this can be achieved by pure SQL. The number of custom fields can increase or decrease depending on the configuration of the product.

Answer 1

Something like this perhaps:

select p.name, age.CFFieldValue, weight.CFFieldValue
from person p
inner join CUSTOMFIELDVALUE age
 on p.id = age.CFFieldName
inner join CUSTOMFIELDS age_f
 on age_f.cfid = age.cfid and age_f.CFFieldName = 'AGE' and age_f.CFTable = 'PERSON'
inner join CUSTOMFIELDVALUE weight
 on p.id = weight.CFFieldName and weight_f.CFFieldName = 'AGE' and weight_f.CFTable = 'PERSON'
inner join CUSTOMFIELDS weight_f
 on weight_f.cfid = weight.cfid;

Its not completely clear to me that person.id joins to customfieldvalue.cfieldname but you do say 100 - 100 - 1001 - 44 so I guess you are trying to say 1001 is the third column? Essentially the query filters the customfieldvalues table and uses it as two separate tables. Which I think is what you want - you question doesn't make it very clear.

Answer 2

It is an interesting problem. You want to change dynamically number and name of columns. This is not possible to create with "normal" SQL. I tried to create sample with using of PIPELINED FUNCTION.

I first created table:

CREATE TABLE PERSON (ID int, NAME varchar2(30));
CREATE TABLE CUSTOMFIELDS (CFID int, CFTable varchar2(30), CFFieldName varchar2(30), CFFieldType varchar2(30));
CREATE TABLE CUSTOMFIELDVALUE (CFVID int, CFID int, CFFieldName varchar2(100), CFFieldValue varchar2(100));

INSERT INTO PERSON(id, name) values(1001, 'Clark Kent');
INSERT INTO CUSTOMFIELDS(CFID, CFTable, CFFieldName, CFFieldType) values(100, 'PERSON', 'AGE', 'INTEGER');
INSERT INTO CUSTOMFIELDS(CFID, CFTable, CFFieldName, CFFieldType) values(200, 'PERSON', 'WEIGHT', 'INTEGER');

...and I placed some data:

INSERT INTO CUSTOMFIELDVALUE (CFVID, CFID, CFFieldName, CFFieldValue) values(100, 100, 1001, 44);
INSERT INTO CUSTOMFIELDVALUE (CFVID, CFID, CFFieldName, CFFieldValue) values(101, 200, 1001, 200);

Then I created an object type:

CREATE TYPE CustomFieldType AS OBJECT
(
  row_id number,
  fieldType varchar2(200),
  person_id number,
  fieldValue1 varchar2(2000),
  fieldValue2 varchar2(2000),
  fieldValue3 varchar2(2000),
  fieldValue4 varchar2(2000),
  fieldValue5 varchar2(2000)
)
/

CREATE TYPE CustomFieldTypeSet AS TABLE OF CustomFieldType
/

And also created PIPELINED FUNCTION:

CREATE OR REPLACE
    FUNCTION GET_PERSON_FIELDS(person_id_in   IN NUMBER
                                    ,field_names_in IN VARCHAR2) RETURN CustomFieldTypeSet
        PIPELINED
    IS

        -- constructor CustomFieldType()
        l_header_row          CustomFieldType := CustomFieldType(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        l_data_row            CustomFieldType := CustomFieldType(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        l_tablen              BINARY_INTEGER;
        l_tab                 DBMS_UTILITY.uncl_array;
        l_num_of_field_values PLS_INTEGER := 5;
        l_counter             PLS_INTEGER := 1;
        l_position            PLS_INTEGER;
        l_field_names_in      VARCHAR2(2000) := field_names_in;

        TYPE type_header_hash IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(200);
        l_header_hash type_header_hash;

    BEGIN

        -- 1) check, what fields you can display

        IF (l_field_names_in IS NULL) THEN

            <<get_all_fields>>
            FOR cur_all_fields IN  (SELECT DISTINCT flds.CFFIELDNAME
                                               FROM CUSTOMFIELDS flds
                                                   ,CUSTOMFIELDVALUE cfv
                                              WHERE cfv.CFID = flds.CFID
                                                AND flds.CFTable = 'PERSON' ) LOOP
                l_field_names_in := l_field_names_in ||
                                    cur_all_fields.CFFIELDNAME ||
                                    ',';
            END LOOP get_all_fields;

        END IF;

        -- 2) generate header (function RTRIM prevent ORA-00931 exception!)

        DBMS_UTILITY.comma_to_table(list => RTRIM(l_field_names_in, ','), tablen => l_tablen, tab => l_tab);
        l_header_row.row_id := 1;
        l_header_row.fieldType := 'HEADER';

        <<header_cursor>>
        FOR i IN 1..l_tablen LOOP

            IF (i = 1) THEN
                l_header_row.fieldValue1 := l_tab(i);
                l_header_hash(l_tab(i)) := i;
            ELSIF (i = 2) THEN
                l_header_row.fieldValue2 := l_tab(i);
                l_header_hash(l_tab(i)) := i;
            ELSIF (i = 3) THEN
                l_header_row.fieldValue3 := l_tab(i);
                l_header_hash(l_tab(i)) := i;
            ELSIF (i = 4) THEN
                l_header_row.fieldValue4 := l_tab(i);
                l_header_hash(l_tab(i)) := i;
            ELSIF (i = 5) THEN
                l_header_row.fieldValue5 := l_tab(i);
                l_header_hash(l_tab(i)) := i;
            END IF;

        END LOOP header_cursor;

        -- 3) print data to SQL (over pipe)...

        PIPE ROW(l_header_row);

        FOR cur_persons IN  (SELECT ID
                               FROM PERSON
                              WHERE ID = COALESCE(person_id_in, ID) ) LOOP
            l_data_row.row_id := NULL;
            l_data_row.person_id := NULL;
            l_data_row.fieldType := NULL;
            l_data_row.fieldValue1 := NULL;
            l_data_row.fieldValue2 := NULL;
            l_data_row.fieldValue3 := NULL;
            l_data_row.fieldValue4 := NULL;
            l_data_row.fieldValue5 := NULL;
            l_data_row.fieldType := 'DATA';
            FOR cur_data IN  (SELECT p.ID AS person_id
                                    ,cfv.CFID
                                    ,flds.CFTABLE
                                    ,flds.CFFIELDNAME
                                    ,cfv.CFFIELDVALUE
                                FROM PERSON p
                                    ,CUSTOMFIELDS flds
                                    ,CUSTOMFIELDVALUE cfv
                               WHERE p.ID = cur_persons.ID
                                 AND p.ID = cfv.CFFIELDNAME
                                 AND cfv.CFID = flds.CFID ) LOOP
                l_data_row.person_id := cur_persons.ID;
                l_position := NULL;

                IF (l_header_hash.EXISTS(cur_data.CFFIELDNAME)) THEN
                    l_position := l_header_hash(cur_data.CFFIELDNAME);
                END IF;

                IF (l_position = 1) THEN
                    l_data_row.fieldValue1 := cur_data.CFFIELDVALUE;
                ELSIF (l_position = 2) THEN
                    l_data_row.fieldValue2 := cur_data.CFFIELDVALUE;
                ELSIF (l_position = 3) THEN
                    l_data_row.fieldValue3 := cur_data.CFFIELDVALUE;
                ELSIF (l_position = 4) THEN
                    l_data_row.fieldValue4 := cur_data.CFFIELDVALUE;
                ELSIF (l_position = 5) THEN
                    l_data_row.fieldValue5 := cur_data.CFFIELDVALUE;
                END IF;

            END LOOP;
            l_counter := l_counter + 1;
            l_data_row.row_id := l_counter;
            PIPE ROW(l_data_row);
        END LOOP;

        RETURN;
    END GET_PERSON_FIELDS;

Than you can use SQL to get sample data (note: prevent exception ORA-22905, you must set session variable "ALTER SESSION SET CURSOR_SHARING=EXACT;"):

SELECT * FROM TABLE(GET_PERSON_FIELDS(1001,'AGE,WEIGHT'));

And here is output:

ROW_ID FIELDTYPE  PERSON_ID FIELDVALUE FIELDVALUE FIELDVALUE
------ ---------- --------- ---------- ---------- ----------
     1 HEADER               AGE
     2 DATA            1001 44

In the first column is header, where is stored information about field names and after header are stored data. You can use combination of these SQLs:

SELECT * FROM TABLE(GET_PERSON_FIELDS(1001,'AGE,WEIGHT'));
SELECT * FROM TABLE(GET_PERSON_FIELDS(1002,'AGE,GENDER'));
SELECT * FROM TABLE(GET_PERSON_FIELDS(1001,NULL));
SELECT * FROM TABLE(GET_PERSON_FIELDS(NULL,NULL));

1. First argument is person_id
2. Second argument is list of items that you want to see the output
3. if first argument is NULL, you can see list of all persons
4. if second argument is NULL, you can see list of all arguments
5. Script is not complete, and have some limitations:
   1. in object CustomFieldType you cannot dynamically change number of fields (I mean fieldValue1, fieldValue2...)
   2. in body of function GET_PERSON_FIELDS as you can see, dynamically problem is also in IF statements (IF (l_position = 1) THEN l_data_row.fieldValue1, IF (l_position = 2) THEN l_data_row.fieldValue1)...

Finally, when I entered some sample data, like this:

INSERT INTO PERSON(id, name) values(1002, 'Lois Lane');
INSERT INTO CUSTOMFIELDS(CFID, CFTable, CFFieldName, CFFieldType) values(300, 'PERSON', 'GENDER', 'VARCHAR');
INSERT INTO CUSTOMFIELDS(CFID, CFTable, CFFieldName, CFFieldType) values(400, 'PERSON', 'SINGLE', 'VARCHAR');
INSERT INTO CUSTOMFIELDVALUE (CFVID, CFID, CFFieldName, CFFieldValue) values(102, 100, 1002, 45);
INSERT INTO CUSTOMFIELDVALUE (CFVID, CFID, CFFieldName, CFFieldValue) values(103, 300, 1002, 'FEMALE');
INSERT INTO CUSTOMFIELDVALUE (CFVID, CFID, CFFieldName, CFFieldValue) values(104, 400, 1002, 'YES');

... and ran this SQL command:

SELECT * FROM TABLE(GET_PERSON_FIELDS(NULL,NULL));

... output looked like this:

ROW_ID FIELDTYPE  PERSON_ID FIELDVALUE FIELDVALUE FIELDVALUE FIELDVALUE
------ ---------- --------- ---------- ---------- ---------- ----------
     1 HEADER               AGE        GENDER     SINGLE     WEIGHT
     2 DATA            1001 44                               200
     3 DATA            1002 45         FEMALE     YES