*!* 示例 1 : 两个字符型字段的转置示例 :
Close Databases All
*!* 1. 建立测试数据 :
Create Cursor Test ( Id C(1), Class C(1) )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "B" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "B" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "A" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Select Distinct Class From Test Into Array laClasses
*!* 2. 构造一个通用的转置函数 :
m.lcSQL = "Select Test.ID "
For Each lcClass In laClasses
m.lcSQL = m.lcSQL + "," + "SUM( IIF(test.Class = [" + lcClass+"], 1, 0) ) As " + lcClass
Endfor
m.lcSQL = m.lcSQL + " From TEST GROUP BY Test.ID"
&lcSQL
*!* 转置结果 :
*!* id A B
*!* ==========
*!* 1 4 2
*!* 2 1 5
*!* 示例 2 : 10个(or 20个)字段的转置示例 :
待续ing..........
*!* 示例 3 : VFP 可以做到而 SQL Server 的 PIVOT() 很难做到的转置示例 :
待续ing..........
Close Databases All
*!* 1. 建立测试数据 :
Create Cursor Test ( Id C(1), Class C(1) )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "B" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "1", "B" )
Insert Into Test ( Id, Class ) Values ( "1", "A" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "A" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Insert Into Test ( Id, Class ) Values ( "2", "B" )
Select Distinct Class From Test Into Array laClasses
*!* 2. 构造一个通用的转置函数 :
m.lcSQL = "Select Test.ID "
For Each lcClass In laClasses
m.lcSQL = m.lcSQL + "," + "SUM( IIF(test.Class = [" + lcClass+"], 1, 0) ) As " + lcClass
Endfor
m.lcSQL = m.lcSQL + " From TEST GROUP BY Test.ID"
&lcSQL
*!* 转置结果 :
*!* id A B
*!* ==========
*!* 1 4 2
*!* 2 1 5
*!* 示例 2 : 10个(or 20个)字段的转置示例 :
待续ing..........
*!* 示例 3 : VFP 可以做到而 SQL Server 的 PIVOT() 很难做到的转置示例 :
待续ing..........
民工子弟学校22班团小组长阳光模特队长冲锋篮球队前锋小苹果合唱队领唱蓝天舞蹈队编舞