数据如下
Name Dest CW Item Cost Item1 Cost1
ABC1 CN 1.00 FSC 1.00 NULL NULL
ABC2 US 3.00 FSC 3.00 SSC 4.00
ABC3 US 3.00 MISC 5.00 NULL NULL
ABC4 SG 2.00 SSC 2.00 NULL NULL希望实现的效果如下
Key Name Item Cost
1 ABC1 FSC 1
2 ABC2 FSC 3
3 ABC2 SSC 4
4 ABC3 MISC 5
5 ABC4 SSC 2
Name Dest CW Item Cost Item1 Cost1
ABC1 CN 1.00 FSC 1.00 NULL NULL
ABC2 US 3.00 FSC 3.00 SSC 4.00
ABC3 US 3.00 MISC 5.00 NULL NULL
ABC4 SG 2.00 SSC 2.00 NULL NULL希望实现的效果如下
Key Name Item Cost
1 ABC1 FSC 1
2 ABC2 FSC 3
3 ABC2 SSC 4
4 ABC3 MISC 5
5 ABC4 SSC 2
select name,item,cost from tb
union all
select name,item1,cost1 from tb
unoin select * from table where Item1 is not null
在处理一下字段名称就可以了
go
create table [tb]([Name] varchar(4),[Dest] varchar(2),[CW] numeric(3,2),[Item] varchar(4),[Cost] numeric(3,2),[Item1] varchar(3),[Cost1] numeric(3,2))
insert [tb]
select 'ABC1','CN',1.00,'FSC',1.00,null,null union all
select 'ABC2','US',3.00,'FSC',3.00,'SSC',4.00 union all
select 'ABC3','US',3.00,'MISC',5.00,null,null union all
select 'ABC4','SG',2.00,'SSC',2.00,null,null
goselect [Key]=row_number() over(order by name,getdate()),*
from
(
select Name, Item, Cost from tb where cost is not null
union all
select Name,item1,cost1 from tb where cost1 is not null
) t
order by 1/**
Key Name Item Cost
-------------------- ---- ---- ---------------------------------------
1 ABC1 FSC 1.00
2 ABC2 FSC 3.00
3 ABC2 SSC 4.00
4 ABC3 MISC 5.00
5 ABC4 SSC 2.00(5 行受影响)
**/
row_number() over(order by name,getdate()) as [key],*
from
(select Name, Item, Cost from tb where cost is not null
union all
select Name,item1,cost1 from tb where cost1 is not null) t
order by
[key]