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 Go to next message
ssyr
Messages: 25
Registered: January 2017
Junior 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 #665301 is a reply to message #665300] Tue, 29 August 2017 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 12959
Registered: September 2008
Location: Rainy Manchester
Senior Member
use listagg and ||
Re: How to show multiple data in single row [message #665327 is a reply to message #665300] Wed, 30 August 2017 03:25 Go to previous messageGo to next message
ssyr
Messages: 25
Registered: January 2017
Junior Member
Thanks. I will try using Listagg.
Re: How to show multiple data in single row [message #665343 is a reply to message #665327] Thu, 31 August 2017 07:27 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
SELECT LISTAGG (Tcode || ':' || TO_CHAR (Parts_amt), '|')
WITHIN GROUP (ORDER BY Tcode DESC)
FROM Trans_parts
GROUP BY Trans_id;
Re: How to show multiple data in single row [message #665353 is a reply to message #665343] Fri, 01 September 2017 01:44 Go to previous message
ssyr
Messages: 25
Registered: January 2017
Junior 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.
Previous Topic: Need help on query
Next Topic: Unexpected weird result A+A' != U
Goto Forum:
  


Current Time: Fri Dec 15 04:04:45 CST 2017

Total time taken to generate the page: 0.09588 seconds