Oracle Database 10g - Query to flatten data 2

[sa0309]

I need a query to Flatten / Pivot the data from TableA ( See Flattened Results). For every PK_PATIENT_LEVELDATA row, there would be a column "Varname" with a value "Value".

Please note: not every PK_PATIENT_LEVELDATA can have 1:N number of VARNAMES/VALUES. Basically trying to create "Dynamic" Columns.

Any help would be appreciated. Thank you in advance

TableA:

PK_PATIENT_LEVELDATA VARNAME VALUE
59 AGE 53
60 AGE 62
61 AGE 65
60 APPTWAIT 5
61 APPTWAIT 10
59 BARCODE 1
60 BARCODE 1
61 BARCODE 1
60 CONTACT No
61 CONTACT No
59 DAYWEEK Saturday-Sunday
60 DAYWEEK MONDAY
61 DAYWEEK WEDNESDAY
59 DISDD 2
60 DISDD 4
61 DISDD 6
59 DISMM 2
60 DISMM 2
61 DISMM 2


_____________________________________________________________________________________________________________________________

Flattened Results:

PK_PATIENT_LEVELDATA AGE APPTWAIT BARCODE CONTACT DAYWEEK DISDD DISMM
59 53 NULL 1 NULL Saturday-Sunday 2 2
60 62 5 1 No MONDAY 4 2
61 65 10 1 No WEDNESDAY 6 2

 

[LKBrwnDBA]

Try this:

SQL> VAR rc REFCURSOR;
SQL> DECLARE
  2    col_lst   VARCHAR2 (1024) := '';
  3  BEGIN
  4    SELECT LISTAGG ( varname, ''',''') WITHIN GROUP (ORDER BY varname)
  5      INTO col_lst
  6      FROM (SELECT DISTINCT varname FROM scott.tablea);
  7
  8    col_lst   := '''' || col_lst || '''';
  9
 10    --
 11    OPEN :rc FOR '
 12  SELECT *
 13    FROM scott.tablea
 14   PIVOT (MAX (vvalue)
 15     FOR varname
 16      IN (' || col_lst || '))';
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL> PRINT rc

PK_PAT_LVL 'AGE'      'APPTWAIT' 'BARCODE'  'CONTACT'  'DAYWEEK'           'DISDD' 'DISMM'
---------- ---------- ---------- ---------- ---------- ------------------- ------- -------
        59 53         1                                Saturday-Sunday     2       2
        60 62         5          1          No         Monday              4       2
        61 65         10         1          No         Wednesday           6       2

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sa0309]

Thank you LKBrwnDBA.

The results are exactly what I'm looking for. I assume you ran this in SQLPLUS? I'm working in SQL Developer....can it be accomplished the with a procedure?

 

[LKBrwnDBA]

Try it.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sa0309]

Apparently Oracle 10g doesn't like the LISTAGG function.

Thanks

 

[LKBrwnDBA]

OK you can replace with:

  4    SELECT Wm_Concat( varname )
  5      INTO col_lst
  6      FROM (SELECT DISTINCT varname FROM scott.tablea);

But you will need to add the quotes.

If wm_concat does not exist, then use:

. . .  sys_connect_by_path(varname,',')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

With WM_CONCAT:

SQL> VAR rc REFCURSOR;
SQL> DECLARE
  2    col_lst   VARCHAR2 (1024) := '';
  3    sql_txt   VARCHAR2 (4096) := '';
  4  BEGIN
  5    SELECT WM_CONCAT( '''' ||varname|| '''')
  6      INTO col_lst
  7      FROM (SELECT DISTINCT varname FROM scott.tablea ORDER BY 1);
  8    --
  9    sql_txt   := '
 10  SELECT *
 11    FROM scott.tablea
 12   PIVOT (MAX (vvalue)
 13     FOR varname
 14      IN (' || col_lst || '))';
 15    dbms_output.put_line('**** SQL Query:'||sql_txt||chr(10)||'------------****');
 16    OPEN :rc FOR sql_txt;
 17  END;
 18  /
**** SQL Query:
SELECT *
  FROM scott.tablea
 PIVOT (MAX (vvalue)
   FOR varname
    IN
('AGE','APPTWAIT','BARCODE','CONTACT','DAYWEEK','DISDD','DISMM'))
------------****

PL/SQL procedure successfully completed.

SQL> print rc

PK_PAT_LVL 'AGE'      'APPTWAIT' 'BARCODE'  'CONTACT'  'DAYWEEK'       'DISDD'    'DISMM'
---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
        59 53                    1                     Saturday-Sunday 2          2
        60 62         5          1          No         Monday          4          2
        61 65         10         1          No         Wednesday       6          2

SQL>

Using SYS_CONNECT_BY_PATH:

SQL> VAR rc REFCURSOR;
SQL> DECLARE
  2    col_lst   VARCHAR2 (1024) := '';
  3    sql_txt   VARCHAR2 (4096) := '';
  4  BEGIN
  5    SELECT SUBSTR ( MAX (SYS_CONNECT_BY_PATH ( '''' || varname || '''', ',')), 2)
  6      INTO col_lst
  7      FROM (SELECT varname, ROW_NUMBER () OVER (ORDER BY varname) rn
  8              FROM (SELECT DISTINCT varname FROM scott.tablea))
  9     START WITH rn = 1
 10        CONNECT BY PRIOR rn = rn - 1;
 11    --
 12    sql_txt   := '
 13  SELECT *
 14    FROM scott.tablea
 15   PIVOT (MAX (vvalue)
 16     FOR varname
 17      IN (' || col_lst || '))';
 18    dbms_output.put_line('**** SQL Query:'||sql_txt||chr(10)||'------------****');
 19    OPEN :rc FOR sql_txt;
 20  END;
 21  /
**** SQL Query:
SELECT *
  FROM scott.tablea
 PIVOT (MAX (vvalue)
   FOR varname
    IN
('AGE','APPTWAIT','BARCODE','CONTACT','DAYWEEK','DISDD','DISMM'))
------------****

PL/SQL procedure successfully completed.

SQL> print rc

PK_PAT_LVL 'AGE'      'APPTWAIT' 'BARCODE'  'CONTACT'  'DAYWEEK'       'DISDD'    'DISMM'
---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
        59 53                    1                     Saturday-Sunday 2          2
        60 62         5          1          No         Monday          4          2
        61 65         10         1          No         Wednesday       6          2

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

LK, Very, very clever solutions, all ! Hava couple of well-deserved

s !

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sa0309]

It is a very clever solution. Thank you. However; PIVOT is a SQL operation, introduced in Oracle 11g and i'm on 10g.

 

[LKBrwnDBA]

Ooops, well DUH!
I saw the 10g in the title but somehow forgot that PIVOT is 11g...
Well sorry for wasting all that time, here is the 10g version:

SQL> VAR rc REFCURSOR;
SQL> DECLARE
  2    col_lst   VARCHAR2 (1024) := '';
  3    sql_txt   VARCHAR2 (4096) := '';
  4  BEGIN
  5    FOR d IN (  SELECT DISTINCT varname
  6                  FROM scott.tablea
  7                 ORDER BY 1)
  8    LOOP
  9      col_lst      := col_lst || CHR (10)||
 10        ', MAX(Decode(varname,''' || d.varname ||''', vvalue,null)) ' || d.varname;
 11    END LOOP;
 12
 13    sql_txt      := '
 14    SELECT PK_PAT_LVL ' || col_lst || '
 15      FROM (SELECT PK_PAT_LVL, varname, vvalue from scott.tablea ORDER BY 1,2)
 16     GROUP BY PK_PAT_LVL';
 17    DBMS_OUTPUT.
 18     put_line ('**** SQL Query:' ||CHR (10)|| sql_txt ||CHR (10)||'------------****');
 19
 20    OPEN :rc FOR sql_txt;
 21  END;
 22  /
**** SQL Query:
  SELECT PK_PAT_LVL
, MAX(Decode(varname,'AGE', vvalue,null)) AGE
, MAX(Decode(varname,'APPTWAIT', vvalue,null)) APPTWAIT
, MAX(Decode(varname,'BARCODE', vvalue,null)) BARCODE
, MAX(Decode(varname,'CONTACT', vvalue,null)) CONTACT
, MAX(Decode(varname,'DAYWEEK', vvalue,null)) DAYWEEK
, MAX(Decode(varname,'DISDD', vvalue,null)) DISDD
, MAX(Decode(varname,'DISMM',
vvalue,null)) DISMM
    FROM (SELECT PK_PAT_LVL, varname, vvalue from scott.tablea ORDER BY 1,2)
   GROUP BY
PK_PAT_LVL
------------****

PL/SQL procedure successfully completed.

SQL> PRINT rc

PK_PAT_LVL AGE             APPTWAIT        BARCODE         CONTACT         DAYWEEK         DISDD           DISMM
---------- --------------- --------------- --------------- --------------- --------------- --------------- -----------
        59 53                              1                               Saturday-Sunday 2               2
        60 62              5               1               No              Monday          4               2
        61 65              10              1               No              Wednesday       6               2

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Hey SA (sounds like something you'd hear in Mexico, huh?) LK has done a mighty good bit of excellent consulting for you. Too bad your company can't send him a check for a couple of hours of consulting! What fine coding, LK !

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

Thanks Dave,
Just shows you the gazillion times I've had to code pivot tables (since 8i).
Best wishes to all!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb