SQL语句

Question

表大体结构如下
维修编号  订单号1 物料编码1   订单号2 物料编码2  订单号3 物料编码3  订单号4 物料编码4  订单号5 物料编码5
1          1000     D1000     1012     D1034      2313     D1234     2311     D2351    null      null
2          1023     D1023     3456     D0234      8923     D2345     null      null    null      null得到结果如下:
编号  订单号  物料编码 
1     1000    D1000
2     1012    D1034
3     2313    D1234
4     2311    D2351
5     1023    D1023
.......

Answer 1

select 编号=row_number() over(order by 维修编号,id),订单号,物料编码
from 
(select 1 as id,维修编号,订单号1 as 订单号,物料编码1 as 物料编码 from tb
union  all
select 2,维修编号,订单号2,物料编码2 from tb
union all
select 3,维修编号,订单号3,物料编码3 from tb
union all
select 4,维修编号,订单号4,物料编码4 from tb
union all
select 5,维修编号,订单号5,物料编码5 from tb) t

Answer 2

row_number函数2000不能用，用sql2000能搞出来吗。

Answer 3

select 编号=IDENTITY(int,1,1),订单号,物料编码 into #tb
from 
(select 1 as id,维修编号,订单号1 as 订单号,物料编码1 as 物料编码 from tb
union  all
select 2,维修编号,订单号2,物料编码2 from tb
union all
select 3,维修编号,订单号3,物料编码3 from tb
union all
select 4,维修编号,订单号4,物料编码4 from tb
union all
select 5,维修编号,订单号5,物料编码5 from tb) t
order by 维修编号,idselect * from #tbdrop table #tb

Answer 4

列转行，再连接表
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([维修编号] int,[订单号1] int,[物料编码1] nvarchar(5),[订单号2] int,[物料编码2] nvarchar(5),[订单号3] int,[物料编码3] nvarchar(5),[订单号4] int,[物料编码4] nvarchar(5),[订单号5] int,[物料编码5] nvarchar(5))
Insert #T
select 1,1000,N'D1000',1012,N'D1034','2313',N'D1234',2311,N'D2351',null,null union all
select 2,1023,N'D1023',3456,N'D0234','8923',N'D2345',null,null,null,null
Go
select 
x.[维修编号],nullif(x.订单号,0) as 订单号,nullif(y.物料编码,'') as 物料编码
from 
(
Select [维修编号],Value as 订单号,row_number()over(order by [维修编号]) as row
from (select [维修编号],[订单号1]=isnull([订单号1],0),[订单号2]=isnull([订单号2],0),[订单号3]=isnull([订单号3],0),[订单号4]=isnull([订单号4],0),[订单号5]=isnull([订单号5],0) from #T)a
unpivot
(Value for 订单号 in([订单号1],[订单号2],[订单号3],[订单号4],[订单号5])) as b
)x
inner join
(
Select [维修编号],Value as 物料编码,row_number()over(order by [维修编号]) as row
from (select [维修编号],[物料编码1]=isnull([物料编码1],''),[物料编码2]=isnull([物料编码2],''),[物料编码3]=isnull([物料编码3],''),[物料编码4]=isnull([物料编码4],''),[物料编码5]=isnull([物料编码5],'') from #T)a
unpivot (Value for 物料编码 in([物料编码1],[物料编码2],[物料编码3],[物料编码4],[物料编码5])) as b
)y on x.row=y.row
/*
1 1000 D1000
1 1012 D1034
1 2313 D1234
1 2311 D2351
1 NULL NULL
2 1023 D1023
2 3456 D0234
2 8923 D2345
2 NULL NULL
2 NULL NULL
*/

Answer 5

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([维修编号] int,[订单号1] int,[物料编码1] nvarchar(5),[订单号2] int,[物料编码2] nvarchar(5),[订单号3] int,[物料编码3] nvarchar(5),[订单号4] int,[物料编码4] nvarchar(5),[订单号5] int,[物料编码5] nvarchar(5))
Insert #T
select 1,1000,N'D1000',1012,N'D1034','2313',N'D1234',2311,N'D2351',null,null union all
select 2,1023,N'D1023',3456,N'D0234','8923',N'D2345',null,null,null,null
Go
select 
[编号]=row_number()over(order by x.[维修编号]),nullif(x.订单号,0) as 订单号,nullif(y.物料编码,'') as 物料编码
from 
(
Select [维修编号],Value as 订单号,row_number()over(order by [维修编号]) as row
from (select [维修编号],[订单号1]=isnull([订单号1],0),[订单号2]=isnull([订单号2],0),[订单号3]=isnull([订单号3],0),[订单号4]=isnull([订单号4],0),[订单号5]=isnull([订单号5],0) from #T)a
unpivot
(Value for 订单号 in([订单号1],[订单号2],[订单号3],[订单号4],[订单号5])) as b
)x
inner join
(
Select [维修编号],Value as 物料编码,row_number()over(order by [维修编号]) as row
from (select [维修编号],[物料编码1]=isnull([物料编码1],''),[物料编码2]=isnull([物料编码2],''),[物料编码3]=isnull([物料编码3],''),[物料编码4]=isnull([物料编码4],''),[物料编码5]=isnull([物料编码5],'') from #T)a
unpivot (Value for 物料编码 in([物料编码1],[物料编码2],[物料编码3],[物料编码4],[物料编码5])) as b
)y on x.row=y.row
where nullif(x.订单号,0) is not null and nullif(y.物料编码,'') is not null
/*
1 1000 D1000
2 1012 D1034
3 2313 D1234
4 2311 D2351
5 1023 D1023
6 3456 D0234
7 8923 D2345
*/
--显示null列,改where条件select 
[编号]=row_number()over(order by x.[维修编号]),nullif(x.订单号,0) as 订单号,nullif(y.物料编码,'') as 物料编码
from 
(
Select [维修编号],Value as 订单号,row_number()over(order by [维修编号]) as row
from (select [维修编号],[订单号1]=isnull([订单号1],0),[订单号2]=isnull([订单号2],0),[订单号3]=isnull([订单号3],0),[订单号4]=isnull([订单号4],0),[订单号5]=isnull([订单号5],0) from #T)a
unpivot
(Value for 订单号 in([订单号1],[订单号2],[订单号3],[订单号4],[订单号5])) as b
)x
inner join
(
Select [维修编号],Value as 物料编码,row_number()over(order by [维修编号]) as row
from (select [维修编号],[物料编码1]=isnull([物料编码1],''),[物料编码2]=isnull([物料编码2],''),[物料编码3]=isnull([物料编码3],''),[物料编码4]=isnull([物料编码4],''),[物料编码5]=isnull([物料编码5],'') from #T)a
unpivot (Value for 物料编码 in([物料编码1],[物料编码2],[物料编码3],[物料编码4],[物料编码5])) as b
)y on x.row=y.row
/*
1 1000 D1000
2 1012 D1034
3 2313 D1234
4 2311 D2351
5 NULL NULL
6 1023 D1023
7 3456 D0234
8 8923 D2345
9 NULL NULL
10 NULL NULL
*/

Answer 6

额，数据库是sql2000，null列不列出来

Answer 7

if not object_id('tb') is null
    drop table tb
Go
Create table tb([维修编号] int,[订单号1] int,[物料编码1] nvarchar(5),[订单号2] int,[物料编码2] nvarchar(5),[订单号3] int,[物料编码3] nvarchar(5),[订单号4] int,[物料编码4] nvarchar(5),[订单号5] int,[物料编码5] nvarchar(5))
Insert tb
select 1,1000,N'D1000',1012,N'D1034','2313',N'D1234',2311,N'D2351',null,null union all
select 2,1023,N'D1023',3456,N'D0234','8923',N'D2345',null,null,null,null
Goselect 编号=IDENTITY(int,1,1),订单号,物料编码 into #tb
from 
(select 1 as id,维修编号,订单号1 as 订单号,物料编码1 as 物料编码 from tb where 订单号1 is not null or 物料编码1 is not null
union  all
select 2,维修编号,订单号2,物料编码2 from tb where 订单号2 is not null or 物料编码2 is not null
union all
select 3,维修编号,订单号3,物料编码3 from tb where 订单号3 is not null or 物料编码3 is not null
union all
select 4,维修编号,订单号4,物料编码4 from tb where 订单号4 is not null or 物料编码4 is not null
union all
select 5,维修编号,订单号5,物料编码5 from tb where 订单号5 is not null or 物料编码5 is not null) t
order by 维修编号,idselect * from #tbdrop table #tb/*
编号          订单号         物料编码
----------- ----------- -----
1           1000        D1000
2           1012        D1034
3           2313        D1234
4           2311        D2351
5           1023        D1023
6           3456        D0234
7           8923        D2345(7 行受影响)

Answer 8

--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([维修编号] int,[订单号1] int,[物料编码1] nvarchar(5),[订单号2] int,[物料编码2] nvarchar(5),[订单号3] int,[物料编码3] nvarchar(5),[订单号4] int,[物料编码4] nvarchar(5),[订单号5] int,[物料编码5] nvarchar(5))
Insert #T
select 1,1000,N'D1000',1012,N'D1034','2313',N'D1234',2311,N'D2351',null,null union all
select 2,1023,N'D1023',3456,N'D0234','8923',N'D2345',null,null,null,null
Go--2000:--动态:declare @s nvarchar(4000)
select @s=
case when colid%2=0 then isnull(@s+' union all ','') else @s end+
 case when colid%2=0 then 
'select row='+RTRIM(Colid)+',[维修编号]' else ',' end+case when LEFT(Name,2)='订单' then ',[订单号]='+quotename(Name)
 else '[物料编码]='+quotename(Name) end +
case when colid%2=1  then ' from #T ' else '' endfrom syscolumns where ID=object_id('Tempdb..#T') and Name not in(N'维修编号')--排除不转换的列
order by Colidexec('select identity(int,1,1 )as 编号,订单号,物料编码 into # from ('+@s+')t  where 订单号 is not null order by [维修编号],row select * from #')--增加一个排序/*
编号 订单号 物料编码
1 1000 D1000
2 1012 D1034
3 2313 D1234
4 2311 D2351
5 1023 D1023
6 3456 D0234
7 8923 D2345
/*