Converting Postgres cross join lateral to SQL server code-CodePudding

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.

CodePudding user response：

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)