Oracle SQL Syntax

Janice Smith
Janice Smith used Ask the Experts™
on
Hello Experts,

I'm trying to write an Oracle SQL 12c query in SQL Developer that will group on a column in a table but output the group values horizontally in separate columns for each group. The query that outputs the data in two vertical columns is below:

select dc.dkt_cd, dx.case_cd
from dktcd dc left outer join dktcascx dx on dc.dkt_cd = dx.dkt_cd
left outer join casecd cc on dx.case_cd = cc.case_cd
group by dc.dkt_cd, dx.case_cd
order by dc.dkt_cd;

Sample output:
DKT_CD            CASE_CD
-----            ------
ACCPT           CVE      
ACCPT           CVF      
ACCPT           CVG      
ACCPT           CVH      
ACCPT           CVHH      
ACCPT           CVI      
ADDUNK          CRA      
ADDUNK          CRB      
ADDUNK          CRBH      
ADDUNK          CRD      
ADDUNK          CRH      
ADDUNK          CRR

Desired output:

DKT_CD            COL1      COL2      COL3      COL4      COL5      COL6       
-----            -----      ----               ----      ----               ----      ----            
ACCPT            CVE      CVF              CVG      CVH      CVHH      CVI
ADDUNK      CRA      CRB      CRBH      CRD      CRH      CRR

Each DKT_CD group can have anywhere from 0 to 63 CASE_CD values. Is this possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Add a ROW_NUMBER() OVER ( PARTITION BY DKT_CD ORDER BY CASE_CD) and use this number to pivot the data.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It's possible to pivot but you'll need to account for all 63 possible values.  You cannot "easily" generate dynamic columns at runtime.  

ste5an's method of pivoting on the number will save you if the codes can change.  You won't have to hard-code the list in the pivot.  However, the report can vary from run to run if the values change.  What used to be column 1 last run can be in a different location if the case_cd in the table changes.

I've never attempted to do this but wanted to mention it since someone else will likely say you "can" do it dynamically:
I say "easily" in quotes because there is the Anton approach that requires creating his custom pivot function:  https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

You can use the function LISTAGG.

Here is a simple example which you can look at.

SELECT
    col1,
    LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1

Regards,
    Tomas Helgi
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>You can use the function LISTAGG.

That will work if you want a CSV list not individual columns and the entire CSV list will not exceed 4000 characters.  In this case I don't think the 4000 character limit will apply but you still don't get individual columns.

If you are going to copy/paste from another site, you should at least tailor the code to the example data provided.  ;)
Senior Developer
Commented:
As PIVOT e.g.

CREATE TABLE SampleData (
    DKT_CD VARCHAR2(255) NOT NULL ,
    CASE_CD VARCHAR2(255) NOT NULL
);

INSERT ALL 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVE' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVF' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVG' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVH' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVH' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ACCPT', 'CVI' ) 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRA' ) 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRB' ) 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRBH' )
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRD' ) 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRH' ) 
  INTO SampleData ( DKT_CD , CASE_CD ) VALUES ( 'ADDUNK', 'CRR' )  
SELECT 1 FROM DUAL;

WITH Data
AS ( SELECT DKT_CD ,
            CASE_CD ,
            ROW_NUMBER() OVER ( PARTITION BY DKT_CD
                                ORDER BY CASE_CD ) AS RN
     FROM   SampleData )
SELECT *
FROM   Data
PIVOT ( MIN(CASE_CD) FOR RN IN ( 1, 2, .., 63 ) ) P;

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
The output you are asking for is a classic "cross tab" output.  Reporting tools like Oracle Reports, Crystal Reports (and probably others) have been able to do this for at least a couple decades.  But, "simple" SQL queries do not do this.  The "pivot" operator that Oracle added a few years ago can do this, but it may require you to list all of the possible expected values.

Here is a query example that uses the "pivot" operator:
select * from (select substr(object_name,1,30) "Table_name", to_char(created, 'YYYY') "Year"
from all_objects
where object_type = 'TABLE')
pivot (count(*) Qty for "Year" in ('2011','2012','2013','2014','2015','2016','2017','2018','2019'))
order by 1 desc, 2 desc;
Janice SmithSystems Analyst

Author

Commented:
Thank you all very much for your help!! I learn so much on Experts Exchange. Greatly appreciated!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial