How To Order Data From Two Columns From Two Different Tables-CodePudding

So I have a column in each table that has a numerical value and I need to order them.

The StepName is the only common value between the two tables

Table1 structure:

StepName | StepOrder
abc        10
ghi        48
jkl        62

Table2 Structure:

StepName | SubStepOrder
abc        1
abc        5
ghi        46
jkl        62

Desired Result:

StepName | Order
abc        10
abc        1
abc        5
ghi        48
ghi        46
jkl        62
jkl        62

I need the step numbers with the substep numbers ordered below them and once there are no more substep numbers we then go to the next step number with it's substepnumbers

If Step 5 had 6 substeps desired result set:

Step 1
Step 2...
...Step 5
SubStep 1...
...SubStep 6
Step 6

CodePudding user response：

You just need to add an additional flag to indicate if each is a step or substep and use in an ordering criteria:

with t as (
    select *, 0 substep
    from t1
    union all 
    select *, 1
    from t2
)
select stepname, steporder
from t
order by stepname,substep,StepOrder

CodePudding user response：

This works if you want to order just by the step order columns and not by the step name.

;WITH d (StepName, StepOrder, SubStepOrder) AS (
    SELECT StepName, StepOrder, NULL
    FROM t1
    UNION ALL
    SELECT t2.StepName, t1.StepOrder, t2.SubStepOrder
    FROM t2
    LEFT JOIN t1 ON t1.StepName = t2.StepName
)
SELECT d.StepName, ISNULL(d.SubStepOrder, d.StepOrder) StepOrder
FROM d
ORDER BY d.StepOrder, d.SubStepOrder

EDIT

Working example:

WITH t1 (StepName, StepOrder) AS (
    SELECT 'abc', 10 UNION
    SELECT 'ghi', 48 UNION
    SELECT 'jkl', 62
), t2 (StepName, SubStepOrder) AS (
    SELECT 'abc', 1 UNION
    SELECT 'abc', 5 UNION
    SELECT 'ghi', 46 UNION
    SELECT 'jkl', 62
) , d (StepName, StepOrder, SubStepOrder) AS (
    SELECT StepName, StepOrder, NULL
    FROM t1
    UNION ALL
    SELECT t2.StepName, t1.StepOrder, t2.SubStepOrder
    FROM t2
    LEFT JOIN t1 ON t1.StepName = t2.StepName
)
SELECT d.StepName, ISNULL(d.SubStepOrder, d.StepOrder) StepOrder
FROM d
ORDER BY d.StepOrder, d.SubStepOrder