已知表 BarCode为条码信息 R1R2列表示正反面 01正面，
id BarCode R1R2
1 A101 01
2 A102 01
3 A103 01
4 A104 01
5 A201 02
6 A202 02
7 A203 02
8 A204 02
需要获得输出，将相同面的BarCode显示在相同列
id R1 R2
1 A101 A201
2 A102 A202
3 A103 A203
4 A104 A204
------解决方案--------------------
有问题 自己屡屡再发~
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-09 11:48:35
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[BarCode] nvarchar(8),[R1R2] nvarchar(4))
insert [huang]
select 1,'A101','01' union all
select 2,'A102','01' union all
select 3,'A103','01' union all
select 4,'A104','01' union all
select 5,'A201','02' union all
select 6,'A202','02' union all
select 7,'A203','02' union all
select 8,'A204','02'
--------------生成数据--------------------------
SELECT a.id,a.r1,b.r2
FROM
(select ROW_NUMBER()OVER(ORDER BY [BarCode])id,[BarCode] AS[R1]
from [huang]
WHERE [R1R2]='01')a full JOIN (SELECT ROW_NUMBER()OVER(ORDER BY [BarCode])id, [BarCode] AS[R2]
from [huang]
WHERE [R1R2]='02')b ON a.id=b.id
----------------结果----------------------------
/*
id r1 r2
-------------------- -------- --------
1 A101 A201
2 A102 A202
3 A103 A203
4 A104 A204
*/
------解决方案--------------------
;with f as
(
select id0=row_number()over(partition by r1r2 order by getdate(),* from tb
)
select
row_number()over(order by getdate()),
a.r1r2 as r1,b.r1r2 as r2
from
(select * from f where r1r2='01') as a
inner join
(select * from f where r1r2='02') as b
on
a.id0=b.id0