CODEWITHSUNDEEP
sqloracle11g
Stef
Stef

Reputation: 139

oracle sql get multiple rows want to set into one single row

I get the following error: ORA-01427: single-row subquery returns more than one row

SQL: 

SELECT t.test 
FROM device t 
JOIN device_interface v on t.pe = v.end_test 
WHERE v.test = upper('IE5656') and t.device_type like '%tin%'

Output: 

T.TEST:
XEM5454
XEM7646

I would like to display it like this in order to not get an error : 

T.TEST:
XEM5454 | XEM7646

OR

T.TEST:
XEM5454, XEM7646

What need to be consider into the sql in order to display it like the example above?

Thank yo uso much for your help and support.

Upvotes: 0

Views: 32

Answers (1)

shrek
shrek

Reputation: 887

Input -

CREATE TABLE tabl1
    (COL1 varchar2(7))
;

INSERT ALL 
    INTO tabl1 (COL1)
         VALUES ('XEM5454')
    INTO tabl1 (COL1)
         VALUES ('XEM7646')
SELECT * FROM dual
;

Query -

select listagg(col1,',')WITHIN GROUP (ORDER BY col1) as value
FROM tabl1;

Output -

VALUE
XEM5454,XEM7646

Upvotes: 1

Related Questions