原帖：http://topic.csdn.net/u/20120526/13/11dbdbd6-c869-4078-8872-427b89ce6f6c.html
- SQL code
--强大的pivot--数据准备create table t4( row int, col int, val char(10))--添加测试数据declare @x intdeclare @y intdeclare @c varchar(6000)set @x = 1while(@x<=9)begin select @[email protected],@c='' while(@y<=9) begin select @c=cast(@x as varchar)+'x'+cast(@y as varchar)+'=' +(case when len(ltrim(@[email protected]))>1 then '' else ' ' end)+ltrim(@[email protected])+' ' select @[email protected]+1 insert into t4 values(@y-1,@x,@c) end select @[email protected]+1end---------------------------------------------row col val----------- ----------- ----------1 1 1x1= 1 2 1 1x2= 2 3 1 1x3= 3 4 1 1x4= 4 5 1 1x5= 5 6 1 1x6= 6 7 1 1x7= 7 8 1 1x8= 8 9 1 1x9= 9 2 2 2x2= 4 3 2 2x3= 6 4 2 2x4= 8 5 2 2x5=10 6 2 2x6=12 7 2 2x7=14 8 2 2x8=16 9 2 2x9=18 3 3 3x3= 9 4 3 3x4=12 5 3 3x5=15 6 3 3x6=18 7 3 3x7=21 8 3 3x8=24 9 3 3x9=27 4 4 4x4=16 5 4 4x5=20 6 4 4x6=24 7 4 4x7=28 8 4 4x8=32 9 4 4x9=36 5 5 5x5=25 6 5 5x6=30 7 5 5x7=35 8 5 5x8=40 9 5 5x9=45 6 6 6x6=36 7 6 6x7=42 8 6 6x8=48 9 6 6x9=54 7 7 7x7=49 8 7 7x8=56 9 7 7x9=63 8 8 8x8=64 9 8 8x9=72 9 9 9x9=81 (45 行受影响)-------------------------------------------------分割线-------------------------------------------==================================================================================================--1.select * from t4 pivot (max(val) for col in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b------------------------------------------------row 9 8 7 6 5 4 3 2 1----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 NULL NULL NULL NULL NULL NULL NULL NULL 1x1= 1 2 NULL NULL NULL NULL NULL NULL NULL 2x2= 4 1x2= 2 3 NULL NULL NULL NULL NULL NULL 3x3= 9 2x3= 6 1x3= 3 4 NULL NULL NULL NULL NULL 4x4=16 3x4=12 2x4= 8 1x4= 4 5 NULL NULL NULL NULL 5x5=25 4x5=20 3x5=15 2x5=10 1x5= 5 6 NULL NULL NULL 6x6=36 5x6=30 4x6=24 3x6=18 2x6=12 1x6= 6 7 NULL NULL 7x7=49 6x7=42 5x7=35 4x7=28 3x7=21 2x7=14 1x7= 7 8 NULL 8x8=64 7x8=56 6x8=48 5x8=40 4x8=32 3x8=24 2x8=16 1x8= 8 9 9x9=81 8x9=72 7x9=63 6x9=54 5x9=45 4x9=36 3x9=27 2x9=18 1x9= 9 (9 行受影响)-------------------------------------------------------2.select * from t4 pivot (max(val) for col in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b-------------------------------------------------------row 1 2 3 4 5 6 7 8 9----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x1= 1 NULL NULL NULL NULL NULL NULL NULL NULL2 1x2= 2 2x2= 4 NULL NULL NULL NULL NULL NULL NULL3 1x3= 3 2x3= 6 3x3= 9 NULL NULL NULL NULL NULL NULL4 1x4= 4 2x4= 8 3x4=12 4x4=16 NULL NULL NULL NULL NULL5 1x5= 5 2x5=10 3x5=15 4x5=20 5x5=25 NULL NULL NULL NULL6 1x6= 6 2x6=12 3x6=18 4x6=24 5x6=30 6x6=36 NULL NULL NULL7 1x7= 7 2x7=14 3x7=21 4x7=28 5x7=35 6x7=42 7x7=49 NULL NULL8 1x8= 8 2x8=16 3x8=24 4x8=32 5x8=40 6x8=48 7x8=56 8x8=64 NULL9 1x9= 9 2x9=18 3x9=27 4x9=36 5x9=45 6x9=54 7x9=63 8x9=72 9x9=81 (9 行受影响)----------------------------------------------------------3.select * from t4 pivot (max(val) for row in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b---------------------------------------------------------col 1 2 3 4 5 6 7 8 9----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x1= 1 1x2= 2 1x3= 3 1x4= 4 1x5= 5 1x6= 6 1x7= 7 1x8= 8 1x9= 9 2 NULL 2x2= 4 2x3= 6 2x4= 8 2x5=10 2x6=12 2x7=14 2x8=16 2x9=18 3 NULL NULL 3x3= 9 3x4=12 3x5=15 3x6=18 3x7=21 3x8=24 3x9=27 4 NULL NULL NULL 4x4=16 4x5=20 4x6=24 4x7=28 4x8=32 4x9=36 5 NULL NULL NULL NULL 5x5=25 5x6=30 5x7=35 5x8=40 5x9=45 6 NULL NULL NULL NULL NULL 6x6=36 6x7=42 6x8=48 6x9=54 7 NULL NULL NULL NULL NULL NULL 7x7=49 7x8=56 7x9=63 8 NULL NULL NULL NULL NULL NULL NULL 8x8=64 8x9=72 9 NULL NULL NULL NULL NULL NULL NULL NULL 9x9=81 (9 行受影响)-----------------------------------------------------------4.select * from t4 pivot (max(val) for row in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b---------------------------------------------------------col 9 8 7 6 5 4 3 2 1----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x9= 9 1x8= 8 1x7= 7 1x6= 6 1x5= 5 1x4= 4 1x3= 3 1x2= 2 1x1= 1 2 2x9=18 2x8=16 2x7=14 2x6=12 2x5=10 2x4= 8 2x3= 6 2x2= 4 NULL3 3x9=27 3x8=24 3x7=21 3x6=18 3x5=15 3x4=12 3x3= 9 NULL NULL4 4x9=36 4x8=32 4x7=28 4x6=24 4x5=20 4x4=16 NULL NULL NULL5 5x9=45 5x8=40 5x7=35 5x6=30 5x5=25 NULL NULL NULL NULL6 6x9=54 6x8=48 6x7=42 6x6=36 NULL NULL NULL NULL NULL7 7x9=63 7x8=56 7x7=49 NULL NULL NULL NULL NULL NULL8 8x9=72 8x8=64 NULL NULL NULL NULL NULL NULL NULL9 9x9=81 NULL NULL NULL NULL NULL NULL NULL NULL(9 行受影响)----------------------------------------------------------------