Oracle SQL Syntax
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?
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?
ste5an
Add a ROW_NUMBER() OVER ( PARTITION BY DKT_CD ORDER BY CASE_CD) and use this number to pivot the data.
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/
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/
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
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
>>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. ;)
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. ;)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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','201
order by 1 desc, 2 desc;
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','201
order by 1 desc, 2 desc;
ASKER
Thank you all very much for your help!! I learn so much on Experts Exchange. Greatly appreciated!