cross apply (
    values
        (col1_id, col1_amt),
        (col2_id, col2_amt),
        (col3_id, col3_amt),
        (col4_id, col4_amt),
        (col5_id, col5_amt),
        (col6_id, col6_amt)
) e (col_id, col_amt)

I want equivalent of this SQL Server syntax in MySQL but can't seem to find any ?

CodePudding user response：

You can use CROSS JOIN LATERAL in MySQL 8.0.14 . VALUES is also supported from 8.0.19 (but you need a ROW constructor)

select *
from (values
    row('a')
) v1(x)
cross join lateral (values
    row (concat(v1.x, 'b'))
) v2(y)

db<>fiddle

So for your example, you can do this

cross join lateral
(
    values
    row(fund1_id, fund1_amt),
    row(fund2_id, fund2_amt),
    row(fund3_id, fund3_amt),
    row(fund4_id, fund4_amt),
    row(fund5_id, fund5_amt),
    row(fund6_id, fund6_amt)
)

For an OUTER APPLY you can use LEFT JOIN LATERAL .... ON 1=1

CodePudding user response：

You need to add ROW before every row:

cross apply (
    values
        ROW(col1_id, col1_amt),
        ROW(col2_id, col2_amt),
        ROW(col3_id, col3_amt),
        ROW(col4_id, col4_amt),
        ROW(col5_id, col5_amt),
        ROW(col6_id, col6_amt)
) e (col_id, col_amt)

EDIT: A simple example to show how VALUES and ROW should work is this DBFIDDLE