String concatenation issue in CTE SQL

Question

I have the following CTE SQL

WITH Tasks AS (
    SELECT     TaskID, ParentTaskID, CAST(SortKey AS nChar) AS sort_key
               /*,cast(SortKey as char) as sort_key */
      FROM     oaTasks AS s
      WHERE    (TaskID = 1)
      UNION ALL
      SELECT   s2.TaskID, s2.ParentTaskID
               ,Cast( '0.'+ cast(Tasks_2.sort_key as NCHAR)
                 + '0.' + cast(s2.SortKey as NCHAR) as NCHAR) AS sort_key
               /*,cast(Tasks_2.sort_key +'.'+ cast(s2.SortKey as char) as char)*/
      FROM     oaTasks AS s2
      INNER JOIN Tasks AS Tasks_2
         ON Tasks_2.TaskID = s2.ParentTaskID
)
SELECT  Tasks_1.TaskID, oaTasks.Task, oaTasks.ParentTaskID
        , oaTasks.SortKey,Tasks_1.sort_key
  FROM  Tasks AS Tasks_1
  INNER JOIN oaTasks ON Tasks_1.TaskID = oaTasks.TaskID

In sort_key column I am not getting the desired format which should be like 01, 01.01, 01.01.01 etc. but I'm getting 1, 0.1, 0.1.1. Any suggestion?

Answer 1

try RIGHT like select RIGHT('00',1,2) gives 01