How to convert mySQL code to Oracle SQL

Question

how can I change mySQL code to work in Oracle SQL 11g

SELECT a.boxid, 
GROUP_CONCAT( CONCAT( b.quantity, \' x \', c.name ) SEPARATOR \', \' ) Items
FROM box a
INNER JOIN item_line b ON a.boxid = b.boxid
INNER JOIN items c ON b.itemid = c.itemid

The output of this query looks like this:

boxid     items
1         2 x ball,4 x bat
2         ball
3         3 x cap,2 x ball,bat
4         2 x ball

Thanks for any answers.

John Woo · Accepted Answer · 2013-05-07 03:09:06Z

1

use LISTAGG()

SELECT  boxid,
        LISTAGG(quantity || ' x ' || name, ',') 
            WITHIN GROUP (ORDER BY boxid) AS items
FROM    tableName
GROUP   BY boxid

edited May 7, 2013 at 3:09

answered May 7, 2013 at 3:03

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WaPaRtY · Accepted Answer · 2013-05-07 02:57:12Z

0

You can try Oracle's SQL Developer Migration (which is a free product). The docs say it will help migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase to Oracle:

http://www.oracle.com/technology/tech/migration//workbench/index%5Fsqldev%5Fomwb.html

answered May 7, 2013 at 2:57

WaPaRtY

5003 silver badges5 bronze badges

1 Comment

Ben D Over a year ago

This link appears to be broken

Collectives™ on Stack Overflow

How to convert mySQL code to Oracle SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related