There are so many ways to convert oracle rows to columns.
Those are
- PIVOT operator
- XMLAGG function
- SYS_CONNECT_BY_PATH operator
- CROSS JOIN
- STRAGG function
- LAG OVER function
- CASE Operator
- DECODE using RANK Operator and may be more…
I have tried only few among the above and am going to give
an example using DECODE and RANK/ROW_NUMBER functions.
Example:
a) Retrieve the data of expenses raised by an employee (row wise
display).
SELECT report_header_id, employee_id, invoice_num
--,(rank() over (partition by employee_id order by
report_header_id)) seq,
-- (row_number() over (partition by employee_id order by
report_header_id))
FROM
ap_expense_report_headers_all
WHERE employee_id = 11923
b) Retrieve the same data in column wise.
SELECT
employee_id, MAX (DECODE (seq, 1,
invoice_num, NULL)) expense_1,
MAX (DECODE (seq, 2,
invoice_num, NULL)) expense_2,
MAX (DECODE (seq, 3,
invoice_num, NULL)) expense_3,
MAX (DECODE (seq, 4,
invoice_num, NULL)) expense_4,
MAX (DECODE (seq, 5,
invoice_num, NULL)) expense_5,
MAX (DECODE (seq, 6,
invoice_num, NULL)) expense_6,
MAX (DECODE (seq, 7,
invoice_num, NULL)) expense_7,
MAX (DECODE (seq, 8,
invoice_num, NULL)) expense_8,
MAX (DECODE (seq, 9,
invoice_num, NULL)) expense9
FROM (SELECT employee_id,
invoice_num,
ROW_NUMBER () OVER (PARTITION BY
employee_id ORDER BY report_header_id)
seq
FROM ap_expense_report_headers_all
WHERE employee_id = 11923)
GROUP BY employee_id;
Same logic applied and generated the tsv(Tab Separated Value) file
using UTL_FILE utility in pl/sql code (i.e. outbound from oracle database).
Sample Code:
CREATE OR REPLACE PROCEDURE xx_barcode_generation_prc (
errbuff OUT VARCHAR2, -- Mandatory parameter for concurrent program executable
registration
retcode OUT VARCHAR2, -- Mandatory parameter for concurrent program executable
registration
p_invoice_no IN VARCHAR2, -- Parameter1 in concurrent program
p_transporter_name IN VARCHAR2, -- Parameter2 in concurrent program
p_form38 IN VARCHAR2
-- Parameter3 in concurrent program
)
IS
-- Getting row values in column wise
CURSOR cur_main (p_invoice_no VARCHAR2)
IS
SELECT DISTINCT a.po_number, a.invoice_no, a.invoice_date,
MAX (DECODE (seq, 1, a.part_no, NULL)) partno_1,
MAX (DECODE (seq, 1, a.quantity, NULL)) qty_1,
MAX (DECODE (seq, 2, a.part_no, NULL)) partno_2,
MAX (DECODE (seq, 2, a.quantity, NULL)) qty_2,
MAX (DECODE (seq, 3, a.part_no, NULL)) partno_3,
MAX (DECODE (seq, 3, a.quantity, NULL)) qty_3,
MAX (DECODE (seq, 4, a.part_no, NULL)) partno_4,
MAX (DECODE (seq, 4, a.quantity, NULL)) qty_4,
MAX (DECODE (seq, 5, a.part_no, NULL)) partno_5,
MAX (DECODE (seq, 5, a.quantity, NULL)) qty_5,
MAX (DECODE (seq, 6, a.part_no, NULL)) partno_6,
MAX (DECODE (seq, 6, a.quantity, NULL)) qty_6,
MAX (DECODE (seq, 7, a.part_no, NULL)) partno_7,
MAX (DECODE (seq, 7, a.quantity, NULL)) qty_7,
MAX (DECODE (seq, 8, a.part_no, NULL)) partno_8,
MAX (DECODE (seq, 8, a.quantity, NULL)) qty_8,
MAX (DECODE (seq, 9, a.part_no, NULL)) partno_9,
MAX (DECODE (seq, 9, a.quantity, NULL)) qty_9
FROM (SELECT po_number,
invoice_no, invoice_date, part_no,
order_line_id, quantity,
RANK () OVER (PARTITION BY invoice_no ORDER BY order_line_id)
seq
FROM (SELECT (CASE
WHEN UPPER (oeh.order_type) LIKE
'%INTERNAL%'
THEN TO_CHAR (wsh.delivery_id)
ELSE oeh.cust_po_number
END
) po_number,
jisp.excise_invoice_no invoice_no,
TO_CHAR
(jisp.excise_invoice_date,
'DD-MON-YYYY'
) invoice_date,
mtl.segment1 part_no,
jisp.quantity quantity,
(
NVL (jisp.selling_price, 0)
* NVL (jisp.quantity, 0)
) invoice_value,
jisp.order_line_id
FROM jai_om_wsh_lines_all jisp,
wsh_new_deliveries_v wsh,
oe_order_headers_v oeh,
mtl_system_items mtl,
oe_order_lines_v oel
WHERE jisp.order_header_id = oeh.header_id
AND jisp.delivery_id = wsh.delivery_id
AND jisp.order_header_id = oel.header_id
AND jisp.order_line_id = oel.line_id
AND jisp.inventory_item_id =
mtl.inventory_item_id
AND mtl.organization_id =
(SELECT inventory_organization_id
FROM financials_system_parameters)
--AND OEH.ORG_ID=NVL(:P_ORG_ID,OEH.ORG_ID)
AND jisp.excise_invoice_no =
NVL (p_invoice_no,
jisp.excise_invoice_no
)
ORDER BY jisp.order_line_id)) a
GROUP BY po_number,
invoice_no, invoice_date;
l_file_name VARCHAR2 (100);
l_file UTL_FILE.FILE_TYPE; -- Data
type of utl_file
l_org_id NUMBER;
l_sysdate DATE;
l_po_number VARCHAR2 (240);
l_invoice_no VARCHAR2 (240);
l_invoice_date DATE;
l_inv_value NUMBER;
l_partno1 VARCHAR2 (240);
l_qty1 NUMBER;
l_partno2 VARCHAR2 (240);
l_qty2 NUMBER;
l_partno3 VARCHAR2 (240);
l_qty3 NUMBER;
l_partno4 VARCHAR2 (240);
l_qty4 NUMBER;
l_partno5 VARCHAR2 (240);
l_qty5 NUMBER;
l_partno6 VARCHAR2 (240);
l_qty6 NUMBER;
l_partno7 VARCHAR2 (240);
l_qty7 NUMBER;
l_partno8 VARCHAR2 (240);
l_qty8 NUMBER;
l_partno9 VARCHAR2 (240);
l_qty9 NUMBER;
l_chr VARCHAR2 (20);
l_error VARCHAR2 (400);
BEGIN
-- Initialize the apps and set org context
-- mo_global.init;
l_org_id := fnd_profile.VALUE ('ORG_ID');
mo_global.set_policy_context ('S', l_org_id);
COMMIT;
-- Generating the Dynamic File Name using Invoice Number and system
date
l_file_name :=
'XX' || '_BARCODE' || l_invoice_no || l_sysdate || '.tsv';
-- Opening file using the utility utl_file in write mode
l_file :=
UTL_FILE.FOPEN
('/data/erp/db/tech_st/11.1.0/appsutil/outbound/ERPTEST',
l_file_name,
'W'
);
OPEN cur_main (p_invoice_no);
FETCH cur_main
INTO l_po_number, l_invoice_no, l_invoice_date, l_partno1, l_qty1,
l_partno2, l_qty2, l_partno3, l_qty3, l_partno4, l_qty4,
l_partno5, l_qty5, l_partno6, l_qty6, l_partno7, l_qty7,
l_partno8, l_qty8, l_partno9, l_qty9;
EXIT WHEN cur_main%NOTFOUND;
fnd_file.put_line (fnd_file.LOG, 'Starting...Inside cursor');
SELECT SYSDATE
INTO l_sysdate
FROM DUAL;
-- To get the TAB
character; ascii value of TAB is 9
SELECT CHR (9)
INTO l_chr
FROM DUAL;
SELECT
ROUND (SUM ((NVL (selling_price, 0) * NVL (quantity, 0))), 2)
INTO l_inv_value
FROM jai_om_wsh_lines_all
WHERE excise_invoice_no = l_invoice_no AND org_id = l_org_id
GROUP BY excise_invoice_no;
fnd_file.put_line
(fnd_file.LOG,
'***********************************************************************'
);
fnd_file.put_line (fnd_file.LOG, 'Opening File : ' || l_file_name);
-- Writing the
file using the data
UTL_FILE.PUT_LINE (l_file,
l_po_number
||
l_chr
||
l_invoice_no
||
l_chr
||
l_invoice_date
||
l_chr
||
p_form38
||
l_chr
||
p_transporter_name
||
l_chr
||
l_inv_value
||
l_chr
||
l_partno1
||
l_chr
||
l_qty1
|| l_partno2
||
l_chr
||
l_qty2
||
l_partno3
||
l_chr
||
l_qty3
||
l_partno4
|| l_chr
||
l_qty4
||
l_partno5
||
l_chr
||
l_qty5
||
l_partno6
||
l_chr
||
l_qty6
||
l_partno7
||
l_chr
||
l_qty7
||
l_partno8
||
l_chr
||
l_qty8
||
l_partno9
|| l_chr
||
l_qty9
);
fnd_file.put_line (fnd_file.LOG, 'Ending File : ' || l_file_name);
fnd_file.put_line
(fnd_file.LOG,
'***********************************************************************'
);
END LOOP ;
UTL_FILE.FCLOSE(l_file);
EXCEPTION
WHEN OTHERS
THEN
l_error := SUBSTR (SQLERRM, 1, 300);
fnd_file.put_line (fnd_file.LOG,
'Other error in XX_BARCODE_GENERATION_PRC: ' || l_error
);
END xx_barcode_generation_prc;
/
Thanks,
Rajesh
Thanks for sharing, it was informative. We play a small role in upskilling people providing the latest tech courses. Join us to upgradeORACLE PAYROLL ONLINE TRAINING
ReplyDeleteNICE ONE ! VIEW MY BLOG AT https://rutujblog.blogspot.com
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks for sharing this blog, this blog is very helpful information for every one.
ReplyDeletepayroll api