Converting Postgres cross join lateral to SQL server code

Question

this is my current code:

                SELECT c.id,
                t.vaccinedate,
                t.vaccine,
                t.vaccinesource,
                t.num
               FROM child c
                 CROSS JOIN LATERAL ( VALUES (c.bcgsource,c.bcgdate,'bcg'::text,1), (c.opv0source,c.opv0date,'opv0'::text,2), (c.penta1source,c.penta1date,'penta1'::text,3), (c.pcv1source,c.pcv1date,'pcv1'::text,4), (c.rota1source,c.rota1date,'rota1'::text,5)) t(vaccinesource, vaccinedate, vaccine, num)

How to convert the same to SQL server code? Because I can't find Cross join lateral in SQL server.

Answer 1

We can try to use CROSS APPLY instead CROSS JOIN LATERAL in SQL server

SELECT c.id,
    t.vaccinedate,
    t.vaccine,
    t.vaccinesource,
    t.num
FROM child c
CROSS APPLY ( VALUES 
    (c.bcgsource, c.bcgdate, 'bcg', 1), 
    (c.opv0source, c.opv0date, 'opv0', 2), 
    (c.penta1source, c.penta1date, 'penta1', 3),
    (c.pcv1source, c.pcv1date, 'pcv1', 4), 
    (c.rota1source, c.rota1date, 'rota1', 5)
) t(vaccinesource, vaccinedate, vaccine, num)