如何显示首先由字符值分隔的列中的数据，然后显示sql server中的数值

Suppose i have a table

假设我有一张桌子

id  Values
1   A
2   B
3   C
4   7
5   J
6   K
7   L

Now i want an output like

现在我想要一个类似的输出

id  Values
1   A
2   B
3   C
4   J
5   K
6   L
7   7

How this can be achieved ?

如何实现这一目标?

1 个解决方案

#1

Generate ROW_NUMBER() with independent sorting on each column using subquery/CTE and join using generated rn:

生成ROW_NUMBER(),使用子查询/ CTE对每列进行独立排序,并使用生成的rn连接:

SqlFiddleDemo

;WITH cte AS
(
  SELECT
   [id],
   rn = ROW_NUMBER() OVER(ORDER BY id)
  FROM tab
)  
,cte2 AS
(
  SELECT
   [values],
   rn = ROW_NUMBER() OVER(ORDER BY CASE
           WHEN  LEFT([values],1) LIKE '%[A-Z]%'
           THEN 1 
           ELSE 2
         END, [values])
  FROM tab
)  

SELECT c1.id, c2.[values]
FROM cte c1
JOIN cte2 c2
  ON c1.rn = c2.rn;