SQL Union tables into treegrid structure for jqGrid

I have created an example with the database structure that I currently have:

My goal is to get a database table with all data already in the right place as well as sorted in the right order to be fed into jqGrid which should look like this at the end:

Entity          |OrderTime  |City   |ProductType    |...
-------------------------------------------------------------------
AlexKlar        |           |       |
   SubPack     |17:00      |London |
       Mango   |           |       |Fruit  |
   WelcomePack |15:00      |London |
       Apple   |           |
       Banana  |           |       |Fruit  |
AnnaKlar        |           |       |
   WelcomePack |16:00      |London |
       Apple   |           |       |Fruit  |
JuliaKlar       |           |       |
   PremiumPack |18:00      |London |
       Lychee  |           |       |Fruit  |
   SubPack     |18:30      |London |
       Mango   |           |       |Fruit  |
   WelcomePack |15:00      |London |
       Apple   |           |       |Fruit  |
       Banana  |           |       |Fruit  |

Question 1: What is the most efficient way to union all tables into one master table so that the id/parents/level/leaf are defined as well for the jgGrid? Since not all levels need a value for all columns, I had to assign an empty string '' to it. My code is currently like this:

SELECT 
CONVERT(VARCHAR,PersonID) AS id, 
0 AS level, 
'false' AS isLeaf, 
'null' AS parent, 
FullName AS entity,
'' AS OrderTime,
'' AS City,
'' AS Active,
'' AS ProductType,
'' AS Price
from Persons
UNION ALL
SELECT 
CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS id, 
1 AS level, 
'false' AS isLeaf, 
CONVERT(VARCHAR,PersonID) AS parent, 
OrderName AS entity,
COALESCE(CAST(OrderTime AS VARCHAR(5)),'') AS OrderTime,
City,
Active,
'' AS ProductType,
'' AS Price
from Orders
UNION ALL
SELECT 
CONVERT(VARCHAR,per.PersonID) + '>' + CONVERT(VARCHAR,ord.OrderID) + '>' + CONVERT(VARCHAR,prod.ProductID) AS id, 
2 AS level, 
'true' AS isLeaf, 
CONVERT(VARCHAR,per.PersonID) + '>' + CONVERT(VARCHAR,ord.OrderID) AS parent, 
ProductName AS entity,
'' AS OrderTime,
'' AS City,
so.Completed AS Active,
ProductType,
COALESCE(CONVERT(VARCHAR,Price),'') AS Price
from SubmittedOrders so
INNER JOIN Orders ord ON ord.OrderID = so.OrderID
INNER JOIN Persons per ON per.PersonID = ord.PersonID
INNER JOIN Products prod ON prod.ProductID = so.ProductID
Order by id

Question2: After creating this table union of the 4 tables that I have, I want to sort it so that it is alphabetically, but only within their own level (so that it looks like the table as illustrated above). I want to avoid using the jqGrid in-built sort function grid.jqGrid('sortGrid', 'importJob'); on the client side, because it is really slow if I have 5000 orders/rows.

Thanks in advance for the help.

Solution

This will get them to sort the way you need:

;WITH
CTE_All
AS
(
SELECT 
    per.PersonID,
    per.FullName,
    per.PersonRow,
    ord.OrderID,
    ord.OrderName,
    ord.OrderTime,
    ord.City,
    ord.Active,
    ord.OrderRow,
    prod.ProductID,
    prod.ProductName,
    prod.ProductType,
    prod.Price,
    prod.ProductRow,
    so.Completed
from #SubmittedOrders so
LEFT JOIN (select *, row_number() over (order by OrderName) OrderRow from #Orders) ord 
ON ord.OrderID = so.OrderID
LEFT JOIN (select *, row_number() over (order by FullName) PersonRow from #Persons) per 
ON per.PersonID = ord.PersonID
LEFT JOIN (select *, row_number() over (order by ProductName) ProductRow from #Products) prod 
ON prod.ProductID = so.ProductID
)
SELECT DISTINCT
    CONVERT(VARCHAR,PersonID) AS id, 
    0 AS level, 
    'false' AS isLeaf, 
    'null' AS parent, 
    FullName AS entity,
    '' AS OrderTime,
    '' AS City,
    '' AS Active,
    '' AS ProductType,
    '' AS Price,
    PersonRow, 
    0 AS OrderRow, 
    0 AS ProductRow
from CTE_All Persons
UNION ALL
SELECT DISTINCT
    CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS id, 
    1 AS level, 
    'false' AS isLeaf, 
    CONVERT(VARCHAR,PersonID) AS parent, 
    OrderName AS entity,
    COALESCE(CAST(OrderTime AS VARCHAR(5)),'') AS OrderTime,
    City,
    Active,
    '' AS ProductType,
    '' AS Price,
    PersonRow, 
    OrderRow, 
    0 AS ProductRow
from CTE_All Orders
UNION ALL
SELECT 
    CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) + '>' + CONVERT(VARCHAR,ProductID) AS id, 
    2 AS level, 
    'true' AS isLeaf, 
    CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS parent, 
    ProductName AS entity,
    '' AS OrderTime,
    '' AS City,
    Completed AS Active,
    ProductType,
    COALESCE(CONVERT(VARCHAR,Price),'') AS Price,
    PersonRow, 
    OrderRow, 
    ProductRow
from CTE_All
Order by PersonRow, OrderRow, ProductRow

You could eliminate the unions by using a group rollup, which creates summary records. But then you have to add some case statements in your select clauses:

select 
    CONVERT(VARCHAR,PersonID) + isnull('>' + CONVERT(VARCHAR,OrderID),'') + isnull('>' + CONVERT(VARCHAR,ProductID),'') AS id,
    Case when ProductID IS NOT null then 2 when OrderID IS NOT null then 1 else 0 end level,
    case when ProductID IS NOT null then 'true' else 'false' end isLeaf,
    case when ProductID IS NOT null then CONVERT(VARCHAR,PersonID) + isnull('>' + CONVERT(VARCHAR,OrderID),'') when OrderID IS NOT null then CONVERT(VARCHAR,PersonID) else 'null' end parent,
    COALESCE(ProductName, OrderName, FullName) entity,
    OrderTime,
    City,
    Active,
    ProductType,
    Price
from
    (
    select #Persons.PersonID, 
        #Orders.OrderID,
        #Products.ProductID, 
        MIN(FullName) FullName,
        CASE WHEN #Orders.OrderID IS NOT NULL THEN MIN(OrderName) END OrderName,
        CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(ProductName) END ProductName,
        CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(convert(varchar(1),Completed)) ELSE '' END Active,
        CASE WHEN #Orders.OrderID IS NOT NULL AND #Products.ProductID IS NULL THEN MIN(OrderTime) ELSE '' END OrderTime,
        CASE WHEN #Orders.OrderID IS NOT NULL AND #Products.ProductID IS NULL THEN MIN(City) else '' END City,
        CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(ProductType) ELSE '' END ProductType,
        CASE WHEN #Products.ProductID IS NOT NULL THEN convert(varchar(20),MIN(Price)) ELSE '' END Price        
    from #SubmittedOrders
    JOIN #Orders
    on #SubmittedOrders.OrderID = #Orders.OrderID
    JOIN #Persons
    on #Orders.PersonID = #Persons.PersonID
    JOIN #Products
    ON #SubmittedOrders.ProductID = #Products.ProductID
    GROUP BY ROLLUP (#Persons.PersonID, #Orders.OrderID,#Products.ProductID)
    having #Persons.PersonID is not null
    ) x
ORDER BY FullName, OrderName, ProductName