SQL Server Practice Activity - Which orders have got both hot and cold items? Pivot and Subqueries

How can we look at conditions in multiple rows at the same time?
My SQL Server Udemy courses are:
----
In this video, we have a number of orders (with an OrderID). In each order, customers have the option of ordering hot or cold items. How can we find all of the orders which includes both hot and cold items?
If you want to try this as an SQL Server Practice Activity, then please use the code below. You should find multiple ways of doing so. In this video:
1. We'll use two subqueries in the WHERE clause,
2. we'll use PIVOT, so that Hot and Cold will be in separate columns and
3. we'll use a subquery in the FROM clause, together with SELECT DISTINCT.
----
Here is the code to create the table. If you are using SQL Server 2014 or earlier, then omit the first line.
DROP TABLE IF EXISTS Orders
CREATE TABLE Orders
(OrderID int,
OrderType varchar(20),
OrderCost decimal(7,2))
INSERT INTO Orders VALUES
(1, 'Hot', 4.56),
(1, 'Cold', 1.24),
(2, 'Hot', 5.12),
(3, 'Cold', 0.99),
(4, 'Hot', 4.54),
(4, 'Hot', 6.98),
(4, 'Cold', 2.24);

SELECT *
FROM Orders

[jacekk]

This was my way before Philip show me correct way :-), its showing me correct OrderId but when I include Order costs I got orderId 4 twice for Cold with price 2.24 :-(
with cte as (
SELECT distinct(OrderID), OrderType, OrderCost
FROM Orders
where OrderType = 'Hot'
),
secondT as (

SELECT distinct(OrderID), OrderType, OrderCost
FROM Orders
where OrderType = 'Cold'
)
select *
from cte
inner join secondT
on cte.OrderID = secondT.OrderID

[GanLinChuMa]

-- intersect

select OrderID
from Orders
where OrderType='Hot'
intersect
select OrderID
from Orders
where OrderType='Cold'
;
thats how i figure out
but intersect can not be used in iq sybase
My company use IQ to do data analysis