分列有关问题

分列问题
部门 数值 部门排序
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