Home » SQL & PL/SQL » SQL & PL/SQL » How to show multiple data in single row (Oracle11g)
How to show multiple data in single row [message #665300] |
Tue, 29 August 2017 07:44 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi All,
CREATE TABLE TRANS_PARTS
(
TRANS_ID NUMBER(38, 0) NOT NULL
, PARTS_AMT NUMBER(38, 8) NOT NULL
, GP_ID NUMBER(38, 8) NOT NULL
, SRC_POSTING DATE NOT NULL ,
, tCODE VARCHAR2(64 BYTE)
, tNAME VARCHAR2(64 BYTE)
)
Insert into TRANS_PARTS values (29580,3749.99,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (29580,266.64,126003,sysdate,'PERIPHERALS','peripherique');
Insert into TRANS_PARTS values (1329,2941.65,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (1329,149.99,126003,sysdate,'PERIPHERALS','peripherique');
Insert into TRANS_PARTS values (6842,1316.66,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (6842,312.49,126009,sysdate,'OSVR','osvr product');
Insert into TRANS_PARTS values (6842,24.96,123727,sysdate,'DEFAULT','Default');
Insert into TRANS_PARTS values (3577,299.96,126003,sysdate,'PERIPHERALS','peripherique');
I have to show output like : SYSTEM:3749.99|PERIPHERALS:266.64
for a particular Transactionid.IF it is single display like SYSTEM:3749.99 .If it has multiple data then separate using pipe symbol.I have given sample script for this.
Could you please suggest me some guide line on this?
Thanks In Advance for your help.
|
|
|
|
|
|
Re: How to show multiple data in single row [message #665353 is a reply to message #665343] |
Fri, 01 September 2017 01:44 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Thanks for your reply.
But my requirement is not satisfied here only.I have to add this column to another query & all column should be display combined data from both query in one query.
WITH wt AS
(SELECT *
FROM test_ve wo
WHERE 1 =1
AND wo.active_ind = 1
),
smdo AS
( SELECT DISTINCT OID,pid FROM wt JOIN demand_tbl smdo
ON wt.OID = smdo.OID AND wt.pid = smdo.pid
WHERE 1 =1
)
SELECT
wt.user_account ,
wt.OID,
CASE
WHEN MAX(wt.sale ) = MAX(bod.tx_curr)
AND wt.sale = sum(bod.tx_ne_price)
THEN 1
WHEN MAX(wt.sale_currency) <> MAX(bod.tx_curr)
AND round(wt.sale_amt,2) = round(sum(bod.tx_ne_price),2)
THEN 2
ELSE 3
END
FROM wt
LEFT JOIN booked_tbl bod
ON wt.OID = bod.OID
AND wt.pid = bod.pid
LEFT JOIN smdo
ON wt.OID = smdo.OID
AND wt.pid = smdo.pid
WHERE 1=1
GROUP BY wt.user_account , wt.OID ,wt.sale
Above query will display 3 column .MY requirement is now I have to display again 1 column from above query(coma separated data)other table.
but in above format i.e ()In this table trans_id column is common how can I display recodes fro only selected oid from above query.
Please help me on this.
Thanks in advance.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:56:28 CDT 2024
|