Pivoting rows into columns dynamically in Oracle

Question

I have the following Oracle 10g table called  kv   select   from  kv  ID       K       V ----     -----   -----   1      name    Bob   1      age     30   1      gender  male   2      name    Susan   2      status  married   I d like to turn my keys into columns using plain SQL  not PL SQL  so that the resulting table would look something like this   ID       NAME    AGE    GENDER  STATUS ----     -----   -----  ------  --------   1      Bob      30     male    2      Susan                   married    The query should have as many columns as unique Ks exist in the table  there aren t that many  There s no way to know what columns may exist before running the query  I m trying to avoid running an initial query to programatically build the final query  The blank cells may be nulls or empty strings  doesn t really matter  I m using Oracle 10g  but an 11g solution would also be ok    There are a plenty of examples out there for when you know what your pivoted columns may be called  but I just can t find a generic pivoting solution for Oracle   Thanks

User · Answer

Happen to have a task on pivot  Below works for me as tested just now on 11g   select   from     select ID  COUNTRY NAME  TOTAL COUNT from ONE TABLE     pivot    SUM TOTAL COUNT  for COUNTRY NAME in        Canada    USA    Mexico

User · Answer

To deal with situations where there are a possibility of multiple values  v in your example   I use PIVOT and LISTAGG   SELECT   FROM     SELECT id  k  v   FROM  kv    PIVOT      LISTAGG v          WITHIN GROUP  ORDER BY k     FOR k IN   name    age   gender   status     ORDER BY id    Since you want dynamic values  use dynamic SQL and pass in the values determined by running a select on the table data before calling the pivot statement

User · Answer

First of all  dynamically pivot using pivot xml again needs to be parsed  We have another way of doing this by storing the column names in a variable and passing them in the dynamic sql as below   Consider we have a table like below      If we need to show the values in the column YR as column names and the values in those columns from QTY  then we can use the below code   declare   sqlqry clob    cols clob  begin   select listagg         YR        as       YR              within group  order by YR    into   cols   from    select distinct YR from EMPLOYEE       sqlqry                select   from           select         from EMPLOYEE       pivot         MIN QTY  for YR in       cols                 execute immediate sqlqry  end      RESULT

User · Answer

Oracle 11g provides a PIVOT operation that does what you want   Oracle 11g solution  select   from  select id  k  v from  kv   pivot max v  for k in   name    age    gender    status      Note  I do not have a copy of 11g to test this on so I have not verified its functionality   I obtained this solution from  http   orafaq com wiki PIVOT  EDIT -- pivot xml option  also Oracle 11g  Apparently there is also a pivot xml option for when you do not know all the possible column headings that you may need   see the XML TYPE section near the bottom of the page located at http   www oracle com technetwork articles sql 11g-pivot-097235 html   select   from  select id  k  v from  kv   pivot xml  max v  for k in  any       Note  As before I do not have a copy of 11g to test this on so I have not verified its functionality   Edit2  Changed v in the pivot and pivot xml statements to max v  since it is supposed to be aggregated as mentioned in one of the comments  I also added the in clause which is not optional for pivot  Of course  having to specify the values in the in clause defeats the goal of having a completely dynamic pivot crosstab query as was the desire of this question s poster

[sql] Pivoting rows into columns dynamically in Oracle

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to pivot