开发者

String concatenation issue in CTE SQL

开发者 https://www.devze.com 2023-01-19 14:13 出处:网络
I have the following CTE SQL WITH Tasks AS ( SELECTTaskID, ParentTaskID, CAST(SortKey AS nChar)开发者_运维百科 AS sort_key

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?


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

0

精彩评论

暂无评论...
验证码 换一张
取 消