×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle Database 10g - Query to flatten data
2

Oracle Database 10g - Query to flatten data

Oracle Database 10g - Query to flatten data

(OP)
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

RE: Oracle Database 10g - Query to flatten data



Try this:

CODE

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> 
3eyes

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

RE: Oracle Database 10g - Query to flatten data

(OP)

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?

RE: Oracle Database 10g - Query to flatten data



Try it.
noevil

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

RE: Oracle Database 10g - Query to flatten data

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

Thanks

RE: Oracle Database 10g - Query to flatten data


OK you can replace with:

CODE

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:

CODE

. . .  sys_connect_by_path(varname,',') 

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

RE: Oracle Database 10g - Query to flatten data



With WM_CONCAT:

CODE

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:

CODE

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> 
medal

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

RE: Oracle Database 10g - Query to flatten data

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

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

RE: Oracle Database 10g - Query to flatten data

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



RE: Oracle Database 10g - Query to flatten data



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:

CODE

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 

thumbsup2

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

RE: Oracle Database 10g - Query to flatten data

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 !

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

RE: Oracle Database 10g - Query to flatten data


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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close