有谁可以实现这个查询功能2table1ab15,00014,00011,762-17,00012,207-11,644-1table2cd15,0004,0001,7627,
有谁可以实现这个查询功能2
table1
a b
15,000 1
4,000 1
1,762 -1
7,000 1
2,207 -1
1,644 -1
table2
c d
15,000
4,000
1,762
7,000
2,207
1,644
怎么通过查询表1，根据表1 b列的值得到表2的格式，用sql语句直接查询出来
[解决办法]
with a(a,b)as(
select 15.000,1 union all
select 4.000, 1 union all
select 1.762,-1 union all
select 7.000, 1 union all
select 2.207,-1 union all
select 1.644,-1)
, tc as(
select number=ROW_NUMBER()over(order by getdate()),* from a
)
select c,d from (
select number,a c,null d from tc where b=1
union all
select number,null,a from tc where b=-1)a
order by number
[解决办法]
select
case when b=1 then a else null end c,
case when b=-1 then a else null end d
from table1
[解决办法]
select case when b=1 then a else 0 end as c,
case when b=-1 then a else 0 end as d
from table1