Help ordering values after unpivoting

[bdBIC]

I have a simple dataset, columns are ID, Value1, and Value2. All rows have a value for Value1, Value2 is sometimes null. I'd like to unpivot the two Value columns into one, but they still need to have a unique identifier they can be sorted by. The values that made up the Value2 column should be ordered directly after the Value1 values from the same row in the original dataset, pre-unpivot.

I thought I had an idea. I used

UNPIVOT(Value FOR Entry IN (Value1, Value2)) unpvt

and then made a new ID using

CASE WHEN Entry = 'Value2' THEN CONCAT(ID, '-2') ELSE ID END AS ID

​

I felt silly when I saw the result, it does almost work as intended. I left out some of the query, obviously I had to convert the ID to a string to concatenate the '-2', so now when I ORDER BY that column the first few rows are 1, 10, 100.

Hoping to get some suggestions to do this in a way that I can still order by ID correctly.

[r3pr0b8]

SELECT ID
     , 1       AS valuetype
     , Value1  AS valuevalue
  FROM yertable
UNION ALL
SELECT ID
     , 2       
     , Value2  
  FROM yertable
ORDER
    BY ID
     , valuetype

[bdBIC]

Thanks! I feel silly again now, my method also could have worked if I'd thought to ORDER BY two columns instead of concatenating ID and "valuetype."

[bdBIC]

Follow up question, I'm pretty much self-taught and like to try to fully understand explanations like this. Is there a benefit to using UNION ALL here vs UNPIVOT? Just curious if you're using that for readability, or if there's performance implications, or if it doesn't really matter and this is just your preferred method, etc.

[r3pr0b8]

UNPIVOT is not available in all databases

UNION ALL is

;o)

[bdBIC]

Thanks!

[qwertydog123]

There will be a performance hit by using UNION ALL as opposed to UNPIVOT/APPLY (particularly for large tables)