That was not my question.
i'm asking how can i achieve this in T-sql 🙂

Reason i want to do this in SQL because these are very heavy queries...

i want clear.
it simple.
i want the head category followed be the sub categories

headcat
subcat 
subcat 
subcat 
subcat 
headcat
subcat 
subcat 
subcat 
headcat

worked.
thanks a lot

now i only got 1 connections string, for the website application and that is the SA !
Which probably very stupid, thinking about it 😉
Would user with less permissions,...

mmmm im talking about a public website, so there no users groups.
can't i create user with read permissions and use that as connection string?
then use that connection string...

Slightly diffrent question, can this also be achieved with the connection string?
if it is possible, how?
and would i be better for performance on a website, have connection string...

I trying make a small change. but without success

the result is this now:
OrderID    Contents    DuplicateOrders
1735733   Apples 1kg   3
1735806    Apples 1kg   3
1735774    Apples 1kg    3
1735756    Oranges 1kg   ...

That Did the Trick, thanks a lot guys 🙂

The previous answer gives me the total options, but not the GROUP of options contained in each order

CREATE TABLE #temptable ( [OrderID] int, [OptionID] int, [Quantity]...

Nope,

I want to know how many orders  (can more then 1 row) have the same products.
So the pack department can do all the same orders in 1...