部门 数值 部门排序
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
------解决方案--------------------
- SQL code
--构建测试数据Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)insert into #TBselect 'A', 2, 1union all select 'B', 41, 2union all select 'C', 51, 3union all select 'D', 16, 4union all select 'E', 11, 5union all select 'F', 12, 6union all select 'G', 13, 7union 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 11B 41 F 12C 51 G 13D 16 H 14(4 行受影响)*/
------解决方案--------------------
- SQL code
Create table #TB(部门 varchar(10), 数值 int, 部门排序 int)insert into #TBselect 'A', 2, 1union all select 'B', 41, 2union all select 'C', 51, 3union all select 'D', 16, 4union all select 'E', 11, 5union all select 'F', 12, 6union all select 'G', 13, 7union all select 'H', 14, 8SELECT a.部门,a.数值,b.部门,b.数值 FROM #TB aLEFT JOIN #TB b ON a.部门排序=b.部门排序-4 WHERE a.部门排序<=4 AND b.部门排序>4
------解决方案--------------------
- SQL code
use tempdbgoCreate table #TB(部门 varchar(10), 数值 int, 部门排序 int)insert into #TBselect 'A', 2, 1union all select 'B', 41, 2union all select 'C', 51, 3union all select 'D', 16, 4union all select 'E', 11, 5union all select 'F', 12, 6union all select 'G', 13, 7union all select 'H', 14, 8;with Tas(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-1where a.gr=0