如何将一列的值拆分为两列

问题描述：

我有一个一列的表格，如下所示

## Temp
-----
ID
-------
1
-2
2
-3
-4
6
8

我希望结果应在两列中以正数作为一列，在负数中作为另一列.

输出应为
正负
---------------
1 -2
2 -3
6 -4
8 NULL

请帮助我

谢谢

I have a table of one column as Shown below

##Temp
-----
ID
-------
1
-2
2
-3
-4
6
8

I want result should be in two columns positive number as one column and negative number as another column.

output should be
Positive Negative
---------------
1 -2
2 -3
6 -4
8 NULL

Please help me

Thanks

答

如果您想按自己的顺序订购商品，可以执行以下操作:

If you want the order as in you question, you could do something like this:

with numTab as (
--setup dummy data
	select
		1 ID
	union all select -2
	union all select 2
	union all select -3
	union all select -4
	union all select 6
	union all select 8
)
select
	posNum.ID Positive,
	negNum.ID Negative
from 
	(select 
		row_number() over(order by ID) rowid,
		ID
	from numTab where ID > = 0) posNum
--use full join instead of left or right join 
full join 
	(select 
		row_number() over(order by ID desc) rowid,
		ID
	from numTab where ID < 0) negNum
	on posNum.rowid = negNum.rowid
;

这应该可以满足您的要求.

That should do what you required.

这里是一种方法:添加两个带有标识值的临时表，一个用于+ ve值，一个用于-ve.分别按升序和降序插入每个值(如果您希望示例中较低-ve的数字与较高+ ve的数字保持一致).然后执行查询以将身份列上的两个表连接起来.

Here''s one way: Add a couple of temporary tables with identity values, one for +ve values and one for -ve. Insert the values from each in ascending and descending order respectively (if you want the lower -ve figures to go with the higher +ve ones as per your example). Then do a query joining the two tables on the identity column.

如何将一列的值拆分为两列

相关推荐