使用什么方法可以将下面 mcode相同的custno 写入到另一个表的不同的字段中 例如
mcode 1 2 3 4 5 6
1501050860000 J057 J1336 J1642 J1881 L0001 L0001
id mcode custno
1 1501050860000 J057
2 1501050860000 J1336
3 1501050860000 J1642
4 1501050860000 J1881
5 1501050860000 L0001
6 1501050860000 L0001
7 1501050860100 J057
8 1501050860200 J057
9 1501050860300 J1881
10 1501050864000 J1881
11 1501050864000 J1882
12 1501050864000 J704
13 1501050864000 J742
14 1501050864000 J1870
15 1501050864000 J1797
16 1501050864000 J1642
17 1501050864000 J1635
18 1501050864000 J1635
19 1501050864000 J1650
20 1501050864000 J1650
21 1501050864000 J1498
22 1501050864000 J1635
23 1501050864000 J1404
24 1501050864000 J144
25 1501050864000 J144
26 1501050864000 J057
27 1501050864000 J076
28 1501050864000 J1336
29 1501050864000 J1263
------解决方案--------------------
insert into 另一个表
select mcode,max(case when (id-1)%6=0 then custno end),
max(case when (id-1)%6=1 then custno end),
max(case when (id-1)%6=2 then custno end),
max(case when (id-1)%6=3 then custno end),
max(case when (id-1)%6=4 then custno end),
max(case when (id-1)%6=5 then custno end)
from 一个表
group by mcode,(id-1)/6
------解决方案--------------------
- SQL code
insert into 另一个表 select mcode,max(case when (id-1)%6=0 then custno end), max(case when (id-1)%6=1 then custno end), max(case when (id-1)%6=2 then custno end), max(case when (id-1)%6=3 then custno end), max(case when (id-1)%6=4 then custno end), max(case when (id-1)%6=5 then custno end)from 一个表group by mcode,(id-1)%6 --这有点笔误
------解决方案--------------------
是不是这个意思：
- SQL code
CREATE TABLE t1( id INT, mcode VARCHAR(20), custno VARCHAR(10))INSERT INTO t1SELECT 1, '1501050860000', 'J057' UNION ALLSELECT 2, '1501050860000', 'J1336' UNION ALLSELECT 3, '1501050860000', 'J1642' UNION ALLSELECT 4, '1501050860000', 'J1881' UNION ALLSELECT 5, '1501050860000', 'L0001' UNION ALLSELECT 6, '1501050860000', 'L0001' UNION ALLSELECT 7, '1501050860100', 'J057' UNION ALLSELECT 8, '1501050860200', 'J057' UNION ALLSELECT 9, '1501050860300', 'J1881'SELECT * FROM t1DECLARE @sql VARCHAR(8000)SET @sql='select mcode'SELECT @[email protected]+',max(case when id='+LTRIM(id)+' then custno else null end) as ['+LTRIM(id)+']'FROM (SELECT DISTINCT id FROM t1) AS aSET @[email protected]+' from t1 group by mcode'PRINT @sqlEXEC (@sql)mcode 1 2 3 4 5 6 7 8 91501050860000 J057 J1336 J1642 J1881 L0001 L0001 NULL NULL NULL1501050860100 NULL NULL NULL NULL NULL NULL J057 NULL NULL1501050860200 NULL NULL NULL NULL NULL NULL NULL J057 NULL1501050860300 NULL NULL NULL NULL NULL NULL NULL NULL J1881
------解决方案--------------------
怎么看都像是做报表，怎么搞这么复杂，如果mcode有一万个，难道写一万个字段？