Лабораторная работа: Новые операторы языка манипулирования данными (DML)

)

SELECT letter, cosmetic_name FROM alphavit,Cosmetics

where cosmetic_name like letter+'%';

4) Оператор PIVOT

SELECT cosmetic_name, [Avon],[Oriflame],[Nivea]

--INTO tmpUnpivot

FROM Cosmetics

PIVOT (

sum(amount)

FOR [firma] IN ([Avon],[Oriflame],[Nivea])

)PVT;

5) Оператор UNPIVOT

SELECT cosmetic_name, firma, amount

FROM tmpUnpivot pvt

UNPIVOT ( amount FOR firma

IN([Avon],[Oriflame],[Nivea])

)unpvt;

6) Оператор CROSS APPLY

alter FUNCTION parf (@cos_id as int)

RETURNS TABLE AS

RETURN

SELECT top(1) cosmetic_name,cosmetic_type

FROM Cosmetics

WHERE cosmetic_id=@cos_id;

SELECT mro.*,price

FROM Parfums

CROSS APPLY parf(parfum_id) as mro;

7) Оператор OUTER APPLY

SELECT mro.*,price