分列问题

[霜之神话]

部门 数值 部门排序
A 2 1
B 41 2
C 51 3
D 16 4
E 11 5
F 12 6
G 13 7
H 14 8
要把这个表分成两列

转换成
部门 数值 部门 数值
A 2 E 11
B 41 F 12
C 51 G 13
D 16 H 14

[DBA_磊仔]

--构建测试数据
Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E', 11, 5
union all select 'F', 12, 6
union all select 'G', 13, 7
union all select 'H', 14, 8

--解决方案
SELECT 
  CAST(SUBSTRING([1], 1, 10)   AS varchar(10)) AS 部门,
  CAST(SUBSTRING([1], 11, 4)   AS int)      AS 数值,
  CAST(SUBSTRING([2], 1, 10)   AS varchar(10)) AS 部门,
  CAST(SUBSTRING([2], 11, 4)   AS int)      AS 数值
 from 
(SELECT CAST(部门 AS BINARY(10))
      + CAST(数值 AS BINARY(4)) AS binstr,
								(row_number()over(order by 部门排序) - 1)%4 as rn,
									ntile(2) over(order by 部门排序) Nt 
	from #TB )a 
pivot (max(binstr) for nt in([1],[2]))p
/*
部门         数值          部门         数值
---------- ----------- ---------- -----------
A          2           E          11
B          41          F          12
C          51          G          13
D          16          H          14

(4 行受影响)

*/

[霜之神话]

如果我两列的行数不一样，左边的多，改怎么写，我一共有68行，左边要有35行，右边33行[Quote=引用 2 楼 的回复:]

SQL code
Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E',……
[/Quote]

[中国风]

use tempdb
go

Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E', 11, 5
union all select 'F', 12, 6
union all select 'G', 13, 7
union all select 'H', 14, 8

;with T
as
(select *,Row=(部门排序-1)%5,gr=(部门排序-1)/5 from #TB)
select a.部门,a.数值,isnull(b.部门,'') as 部门2,isnull(rtrim(b.数值),'') as  数值
from T as a 
left join T as b on a.Row=b.Row and a.gr=b.gr-1
where a.gr=0

-- 68记录 这样改
(部门排序-1)%35,gr=(部门排序-1)/35

[中国风]

;with T
as
(select *,Row=(部门排序-1)%35,gr=(部门排序-1)/35 from #TB)
select a.部门,a.数值,isnull(b.部门,'') as 部门2,isnull(rtrim(b.数值),'') as  数值
from T as a 
left join T as b on a.Row=b.Row and a.gr=b.gr-1
where a.gr=0

--35 为左边显示记录

[ttpsan550]

引用2楼，把里面的4改成35不就好了

Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E', 11, 5
union all select 'F', 12, 6
union all select 'G', 13, 7
union all select 'H', 14, 8

SELECT a.部门,a.数值,b.部门,b.数值 FROM #TB a
LEFT JOIN #TB b ON a.部门排序=b.部门排序-35
 WHERE a.部门排序<=35 AND b.部门排序>35

[霜之神话]

自己解决了，谢谢2楼[Quote=引用 2 楼 的回复:]

SQL code
Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E',……
[/Quote]

[GorillazForthgoer]

Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)
insert into #TB
select 'A', 2, 1
union all select 'B', 41, 2
union all select 'C', 51, 3
union all select 'D', 16, 4
union all select 'E', 11, 5
union all select 'F', 12, 6
union all select 'G', 13, 7
union all select 'H', 14, 8

SELECT a.部门,a.数值,b.部门,b.数值 FROM #TB a
LEFT JOIN #TB b ON a.部门排序=b.部门排序-4
 WHERE a.部门排序<=4 AND b.部门排序>4