Splitting rows to columns in oracle

问题:

I have data in a table which looks like:

I want to split its data and make it look like the following through a sql query in Oracle (without using pivot):

How can it be done?? is there any other way of doing so without using pivot?

回答1:

You need to use a pivot query here to get the output you want:

SELECT Name,
       MIN(CASE WHEN ID_Type = 'PAN'      THEN ID_No ELSE NULL END) AS PAN,
       MIN(CASE WHEN ID_Type = 'DL'       THEN ID_No ELSE NULL END) AS DL,
       MIN(CASE WHEN ID_Type = 'Passport' THEN ID_No ELSE NULL END) AS Passport
FROM yourTable
GROUP BY Name

You could also try using Oracle's built in PIVOT() function if you are running version 11g or later.

回答2:

Since you mention without using PIVOT function, you can try to use SQL within group for moving rows onto one line and listagg to display multiple column values in a single column.

In Oracle 11g, we can use the listagg built-in function :

select
    deptno,
    listagg (ename, ',') 
    WITHIN GROUP 
    (ORDER BY ename) enames
FROM 
 emp
GROUP BY 
 deptno

Which should give you the below result:

DEPTNO ENAMES                                            
------ --------------------------------------------------
    10 CLARK,KING,MILLER                                 
    20 ADAMS,FORD,JONES,SCOTT,SMITH                
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD     

You can find all the solution(s) to this problem here: http://www.dba-oracle.com/t_converting_rows_columns.htm

回答3:

For Oracle 11g and above, you could use PIVOT.

For pre-11g release, you could use MAX and CASE.

A common misconception, about PIVOT better in terms of performance than the old way of MAX and DECODE. But, under the hood PIVOT is same MAX + CASE. You can check it in 12c where Oracle added EXPAND_SQL_TEXT procedure to DBMS_UTILITY package.

For example,

SQL> variable c clob
SQL> begin
  2      dbms_utility.expand_sql_text(Q'[with choice_tbl as (
  3                      select 'Jones' person,1 choice_nbr,'Yellow' color from dual union all
  4                      select 'Jones',2,'Green' from dual union all
  5                      select 'Jones',3,'Blue' from dual union all
  6                      select 'Smith',1,'Orange' from dual
  7                     )
  8  select  *
  9    from  choice_tbl
 10    pivot(
 11          max(color)
 12          for choice_nbr in (1 choice_nbr1,2 choice_nbr2,3 choice_nbr3)
 13         )]',:c);
 14  end;
 15  /

PL/SQL procedure successfully completed.

Now let's see what Oracle actually does internally:

SQL> set long 100000
SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."PERSON" "PERSON",
        "A1"."CHOICE_NBR1" "CHOICE_NBR1",
        "A1"."CHOICE_NBR2" "CHOICE_NBR2",
        "A1"."CHOICE_NBR3" "CHOICE_NBR3"
  FROM  (
         SELECT  "A2"."PERSON" "PERSON",
                 MAX(CASE  WHEN ("A2"."CHOICE_NBR"=1) THEN "A2"."COLOR" END ) "CHOICE_NBR1",
                 MAX(CASE  WHEN ("A2"."CHOICE_NBR"=2) THEN "A2"."COLOR" END ) "CHOICE_NBR2",
                 MAX(CASE  WHEN ("A2"."CHOICE_NBR"=3) THEN "A2"."COLOR" END ) "CHOICE_NBR3"
          FROM  (
                 (SELECT 'Jones' "PERSON",1 "CHOICE_NBR",'Yellow' "COLOR" FROM "SYS"."DUAL" "A7") UNION ALL
                 (SELECT 'Jones' "'JONES'",2 "2",'Green' "'GREEN'" FROM "SYS"."DUAL" "A6") UNION ALL
                 (SELECT 'Jones' "'JONES'",3 "3",'Blue' "'BLUE'" FROM "SYS"."DUAL" "A5") UNION ALL
                 (SELECT 'Smith' "'SMITH'",1 "1",'Orange' "'ORANGE'" FROM "SYS"."DUAL" "A4")
                ) "A2"
          GROUP BY "A2"."PERSON"
       ) "A1"
SQL>

Oracle internally interprets the PIVOT as MAX + CASE.

回答4:

You're able to create a non-pivot query by understanding what the pivot query will do:

select *
from yourTable
pivot
(
  max (id_no)
  for (id_type) in ('PAN' as pan, 'DL' as dl, 'Passport' as passport)
)

What the pivot does is GROUP BY all columns not specified inside the PIVOT clause (actually, just the name column), selecting new columns in a subquery fashion based on the aggregations before the FOR clause for each value specified inside the IN clause and discarding those columns specified inside the PIVOT clause.

When I say "subquery fashion" I'm refering to one way to achieve the result got with PIVOT. Actually, I don't know how this works behind the scenes. This subquery fashion would be like this:

select <aggregation>
from <yourTable>
where 1=1 
  and <FORclauseColumns> = <INclauseValue>
  and <subqueryTableColumns> = <PIVOTgroupedColumns>

Now you identified how you can create a query without the PIVOT clause:

select 
  name,
  (select max(id_no) from yourTable where name = t.name and id_type = 'PAN') as pan,
  (select max(id_no) from yourTable where name = t.name and id_type = 'DL') as dl,
  (select max(id_no) from yourTable where name = t.name and id_type = 'Passport') as passport
from yourTable t
group by name

回答5:

You can use CTE's to break the data down and then join them back together to get what you want:

WITH NAMES AS (SELECT DISTINCT NAME
                 FROM YOURTABLE),
     PAN AS (SELECT NAME, ID_NO AS PAN
               FROM YOURTABLE
               WHERE ID_TYPE = 'PAN'),
     DL AS (SELECT NAME, ID_NO AS DL
              FROM YOURTABLE
              WHERE ID_TYPE = 'DL'),
     PASSPORT AS (SELECT NAME, ID_NO AS "Passport"
                    FROM YOURTABLE
                    WHERE ID_TYPE = 'Passport')
SELECT n.NAME, p.PAN, d.DL, t."Passport"
  FROM NAMES n
  LEFT OUTER JOIN PAN p
    ON p.NAME = n.NAME
  LEFT OUTER JOIN DL d
    ON d.NAME = p.NAME
  LEFT OUTER JOIN PASSPORT t
    ON t.NAME = p.NAME'

Replace YOURTABLE with the actual name of your table of interest.

Best of luck.