Create function that returns table in SQL

Question

I wanted to create view with some logic like using (for loop , if .. else) but since that's not supported in SQL I thought of creating table function that takes no parameter and returns a table.

I have a table for orders as below

OrderId  Destination  Category  Customer
----------------------------------------
6001     UK           5         Adam
6002     GER          3         Jack

And table for tracking orders as below

ID  OrderID  TrackingID
-----------------------
1   6001     1
2   6001     2
3   6002     2

And here are the types of tracking

ID  Name
--------------
1   Processing
2   Shipped
3   Delivered

As you can see in tracking order, The order number may have more than one record depending on how many tracking events occurred.

We have more than 25 tracking types that I didn't include here. which means one order can exist 25 times in tracking order table.

Now with that being said , My requirements is to create view as below with condition that an order must belong to 5 or 3 category ( we have more than 15 categories).

And whenever I run the function it must return the updated information.

So for example, when new tracking occurs and it's inserted in tracking order , I want to run my function and see the update in the corresponding flag column (e.g isDelivered).

I'm really confused on what is the best way to achieve this. I don't need the exact script i just need to understand the way to achieve it as i'm not very familiar with SQL

Answer 1

It could be done with a crosstab query using conditional aggregation. Something like this

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;

[EDIT] To break out Category 3 orders, 3 additional columns were added to the cross tab.

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered,
       max(case when tt.[Name]='Processing' and o.category=3 then 1 else 0 end) isC3Prepared,
       max(case when tt.[Name]='Shipped' and o.category=3 then 1 else 0 end) isC3Shipped,
       max(case when tt.[Name]='Delivered'  and o.category=3 then 1 else 0 end) isC3Delivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;