｛Q｝将Single Column值转换为3个不同的Column.即数字、字符、符号

Question

Col1
1
A
@           Number  |   Char   |  Symbol
2       =>   1        A         @
B                2        B     !
!                3        C     $
3                4        D     %
C
$
4
D
%

在此处输入图像描述

Answer 1

一个有趣的问题，有同样有趣的答案
以下是需要做的事情
第一列只能是一个数字
第二个需要从下一行开始
第三个需要从数字中减去2

我创建了一个临时表来填充测试数据。下面是这个代码的样子：

Declare @TestData TABLE
(
Col1       varchar(5)
);
INSERT INTO @TestData (Col1) select '1'
INSERT INTO @TestData (Col1) select 'A'
INSERT INTO @TestData (Col1) select '@'
INSERT INTO @TestData (Col1) select '2'
INSERT INTO @TestData (Col1) select 'B'
INSERT INTO @TestData (Col1) select '!'
INSERT INTO @TestData (Col1) select '3'
INSERT INTO @TestData (Col1) select 'C'
INSERT INTO @TestData (Col1) select '$'
INSERT INTO @TestData (Col1) select '4'
INSERT INTO @TestData (Col1) select 'D'
INSERT INTO @TestData (Col1) select '%'
Declare @TestData2 TABLE
(
Col1    varchar(5),
myrow   integer
)
INSERT INTO @TestData2 (Col1, myrow) 
select t1.*,
ROW_NUMBER() over(order by (select null)) as myrow
from @TestData as t1
select distinct s1.Col1 as 'Number', 
s2.Col1 as 'Char', 
s3.Col1 as 'Symbol' 
from @TestData2 as s1
left join @TestData2 as s2
on s2.myrow = s1.myrow +1
left join @TestData2 as s3
on s3.myrow = s1.myrow +2
where s1.Col1 between '0' and '99999'

结果如下：

符号12B34