Oracle SQL Developer - Pivot table without aggregation function

Question

I have the following table (simplified from reality).

 | ID | String_AttributeName | String_AttributeValue | Int_AttributeName | Int_AttributeValue |
 |----|----------------------|-----------------------|-------------------|--------------------|
 | 1  | Name                 | Object1               |                   |                    |
 | 1  | (null)               | (null)                | Age               | 5                  |
 | 2  | Name                 | Object2               |                   |                    |
 | 2  | Location             | Canada                | (null)            | (null)             |
 | 2  | (null)               | (null)                | Quantity          | 2                  |
 | 3  | Name                 | Object3               |                   |                    |
 | 3  | (null)               | (null)                | Age               | 3                  |
 | 3  | (null)               | (null)                | Quantity          | 4                  |

Basically, I have multiple items (which are differentiated by the ID column) described in the table by the 4 attribute columns.

The way it works is that for each AttributeName column, there is a value, represented by the AttributeValue column. Each attribute is separated according to its type (there are string, int, bool, etc. I will only show 2 to simplify the problem).

When I say AttributeName, it means either column #2 or #4. Every item (ID column) is described in multiple lines, where each line may only contain one attribute (the other will be null). Therefore, There may not be 2 AttributeName in the same row. Also, not all objects have every existing attribute (ID 3 has all attributes while 1 and 2 don't); however, doing a DISTINCT SELECT in the AttributeName of either type will select every AttributeName possibility

My goal is the following (explications below):

+----+---------+--------+----------+
| ID | Name    | Age    | Quantity |
+----+---------+--------+----------+
| 1  | Object1 | 5      | (null)   |
+----+---------+--------+----------+
| 2  | Object2 | (null) | 2        |
+----+---------+--------+----------+
| 3  | Object3 | 3      | 4        |
+----+---------+--------+----------+

As you can see, I want each attribute, no matter its type (int, string, etc.) to be a column and the result be in the corresponding row of every item. Also, there are more than 50 different attributes and they can change with time, so something that changes dynamically would be the best.

I've tried the PIVOT function, but, first, I don't think that it will work, since there is no aggregation, and second, i do not want a null column. My query is also not functioning, since there is a "missing expression"

Here it is:

SELECT  *
FROM   
(
    SELECT  ID
            ,STRING_ATTRIBUTENAME
    FROM    PIVOTTABLE
)
PIVOT
(
    COUNT(STRING_ATTRIBUTENAME)
    FOR STRING_ATTRIBUTENAME IN (SELECT DISTINCT PIVOTTABLE.STRING_ATTRIBUTENAME     FROM PIVOTTABLE)
)
ORDER BY PARTNUMBER;

It's been a while I last did SQL development (I used to do SQL Server), so it is a bit rusty. I just have no clue of else to try, I'm currently looking if there is a way to do some dynamic SQL with a select of the AttributeName column.

Any clues will also help. Thanks !

------------ Here is for recreating the start table:

CREATE TABLE PIVOTTABLE 
(
  ID NUMBER NOT NULL 
, STRING_ATTRIBUTENAME VARCHAR2(50) 
, STRING_ATTRIBUTEVALUE VARCHAR2(50) 
, INT_ATTRIBUTENAME VARCHAR2(50) 
, INT_ATTRIBUTEVALUE NUMBER 
);

INSERT INTO TestTable VALUES('1','Name','Object1','','')
INSERT INTO TestTable VALUES('1','','','Age','5')
INSERT INTO TestTable VALUES('2','Name','Object2','','')
INSERT INTO TestTable VALUES('2','','','Quantity','2')
INSERT INTO TestTable VALUES('3','Name','Object3','','')
INSERT INTO TestTable VALUES('3','','','Age','3')
INSERT INTO TestTable VALUES('3','','','Quantity','4')

This is the dreaded Entity-Attribute-Value anti-pattern. If this system you are working on is not in production yet, stop and _seriously_ considering redesigning it. You are on the road to a lot of overly complicated queries, limited referential integrity, and terrible performance. If you are already committed, see this link for a dynamic `pivot` function using the Oracle Data Cartridge Interface. I've used it in Production and it worked well. — Matthew McPeak, Jun 18 '18 at 21:51
@Matthew McPeak : You forgot to include the link? I wished to see it :-) — Kaushik Nayak, Jun 19 '18 at 03:15
Sorry. Here: https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ — Matthew McPeak, Jun 19 '18 at 04:34
Sadly, I just started working here and this system is deeply implemented, so there is no way dodge it. Thanks for the link, I'll provide more information if it works. Thanks! — RegularNormalDayGuy, Jun 19 '18 at 11:40

score 1 · Answer 1 · answered Jun 18 '18 at 20:23

1

Here's one option using conditional aggregation:

select id, 
       max(case when string_attributename = 'Name' then string_attributevalue end) as Name,
       max(case when int_attributename = 'Age' then int_attributevalue end) as Age,
       max(case when int_attributename = 'Quantity' then int_attributevalue end) as Quantity
from pivottable
group by id

answered Jun 18 '18 at 20:23

sgeddes

62,311
6
61
83

I thought of that, but there are many possibilities for the attributes (about 50) and they may or may not change with time. That's why I was looking into a dynamic query with a `SELECT DISTINCT` to set columns. I will however add the fact that there are many attributes in the real case to the question. Thanks! – RegularNormalDayGuy Jun 18 '18 at 20:28

score 1 · Accepted Answer · answered Jun 18 '18 at 21:34

Using PL/SQL and Dynamic Queries -

CREATE OR REPLACE PROCEDURE TMP_C(P OUT SYS_REFCURSOR)AS
    QRY VARCHAR2(1024) := 'SELECT ID ';
    C SYS_REFCURSOR;

BEGIN

FOR R IN (SELECT DISTINCT STRING_ATTRIBUTENAME FROM (
            SELECT ID, STRING_ATTRIBUTENAME, STRING_ATTRIBUTEVALUE
            FROM TABLE1
            WHERE STRING_ATTRIBUTENAME IS NOT NULL
            UNION
            SELECT ID, INT_ATTRIBUTENAME, INT_ATTRIBUTEVALUE
            FROM TABLE1
            WHERE INT_ATTRIBUTENAME IS NOT NULL))
LOOP
    QRY := QRY ||REPLACE(', MAX(DECODE(STRING_ATTRIBUTENAME,''$X'',STRING_ATTRIBUTEVALUE)) AS $X '
                       ,'$X',R.STRING_ATTRIBUTENAME );

END LOOP;
QRY := QRY ||' FROM ((
SELECT ID, STRING_ATTRIBUTENAME, STRING_ATTRIBUTEVALUE
FROM TABLE1
WHERE STRING_ATTRIBUTENAME IS NOT NULL
UNION
SELECT ID, INT_ATTRIBUTENAME, INT_ATTRIBUTEVALUE
FROM TABLE1
WHERE INT_ATTRIBUTENAME IS NOT NULL)) GROUP BY ID ';

OPEN P FOR QRY;

END;
/

Execution -

SQL> variable x refcursor
SQL> exec tmp_c(:x);

PL/SQL procedure successfully completed.

SQL> print x

        ID QUANTIT LOCATIO AGE     NAME
---------- ------- ------- ------- -------
         1                 5       Object1
         2 2       Canada          Object2
         3 4               3       Object3

SQL>

It compiles with the following errors: 1- ``7/11 PL/SQL: SQL Statement ignored`` 2- ``8/46 PL/SQL: ORA-01790: expression must have same datatype as corresponding expression`` 3- ``16/5 PL/SQL: Statement ignored`` 4- ``17/30 PLS-00364: loop index variable 'R' use is invalid`` — RegularNormalDayGuy, Jun 19 '18 at 12:37
Alright, I fixed it by casting INT_ATTRIBUTEVALUE as a varchar2(50). That should work, thanks a lot ! — RegularNormalDayGuy, Jun 19 '18 at 12:54

Oracle SQL Developer - Pivot table without aggregation function

2 Answers2