求交叉表算法（象DecisionCube） ( 积分: 100 )

a table
==============
row_column Col_column value
a 1 v1
a 2 v2
b 1 v3
b 2 v4
key字段 为 row_column和 Col_column
想把它分析到grid中，像这样：
1 2
=======================
a v1 v2
b v3 v4
现有我的算法
扫描row_column,找出唯一，扫描Col_column ，找出唯一
扫描value，填到表中
效率不高，谁提供更好的算法
procedure LoadData(CDS:TClientDataSet;aStringGrid:TStringGrid;CrossTabDesc,RowField,ColField,ValueField,StatusField:String;
var aStatus:TSelfStatus);
var
i,iRow,jCol:Integer;
RowName,ColName,Status:String;
RowString,ColString:TStrings;
// origColor:TColor;
// Rect:TRect;
begin
RowString:=TStringList.Create;
ColString:=TStringList.Create;
try
//扫描行
CDS.First;
while not cds.Eofdo
begin
if RowString.IndexOf(CDS.FieldByName(RowField).AsString)=-1 then
RowString.Add(CDS.FieldByName(RowField).AsString);
CDS.Next;
end;

//扫描列
CDS.First;
while not cds.Eofdo
begin
if ColString.IndexOf(CDS.FieldByName(ColField).AsString)=-1 then
ColString.Add(CDS.FieldByName(ColField).AsString);
CDS.Next;
end;
with aStringGriddo
begin
//填充单元格
RowCount:=RowString.Count+1;
ColCount:=ColString.Count+1;
for jCol:=0 to ColCount-1do
for iRow:=0 to RowCount-1do
Cells[jCol,iRow]:='';
SetLength(aStatus,ColCount,RowCount);
if (RowCount<=1) then
FixedRows:=0
else
FixedRows:=1;
if (ColCount<=1)then
FixedCols:=0
else
FixedCols:=1;
Cells[0,0] := CrossTabDesc;
//填充行
for i:=0 to RowString.Count-1do
Cells[0,i+1]:=RowString;
//填充列
for i:=0 to ColString.Count-1do
Cells[i+1,0]:=ColString;
//填充值
CDS.First;
for i:=0 to CDS.RecordCount-1do
begin
RowName:=CDS.fieldbyname(RowField).AsString;
ColName:=CDS.fieldbyname(ColField).AsString;
Status:=CDS.fieldbyname(StatusField).AsString;
for iRow:=0 to RowCount-1do
if Cells[0,iRow]=RowName then
break;
for jCol:=0 to ColCount-1do
if Cells[jCol,0]=ColName then
break;
aStatus[jCol,iRow]:=Status;
Cells[jCol,iRow]:=CDS.FieldByName(ValueField).AsString;
CDS.Next;
end;
end;
finally
RowString.Free;
ColString.Free;
end;
end;

a table
==============
row_column Col_column value
a 1 v1
a 2 v2
b 1 v3
b 2 v4
key字段 为 row_column和 Col_column
想把它分析到grid中，像这样：
1 2
=======================
a v1 v2
b v3 v4
现有我的算法
扫描row_column,找出唯一，扫描Col_column ，找出唯一
扫描value，填到表中
效率不高，谁提供更好的算法
procedure LoadData(CDS:TClientDataSet;aStringGrid:TStringGrid;CrossTabDesc,RowField,ColField,ValueField,StatusField:String;
var aStatus:TSelfStatus);
var
i,iRow,jCol:Integer;
RowName,ColName,Status:String;
RowString,ColString:TStrings;
// origColor:TColor;
// Rect:TRect;
begin
RowString:=TStringList.Create;
ColString:=TStringList.Create;
try
//扫描行
CDS.First;
while not cds.Eofdo
begin
if RowString.IndexOf(CDS.FieldByName(RowField).AsString)=-1 then
RowString.Add(CDS.FieldByName(RowField).AsString);
CDS.Next;
end;

//扫描列
CDS.First;
while not cds.Eofdo
begin
if ColString.IndexOf(CDS.FieldByName(ColField).AsString)=-1 then
ColString.Add(CDS.FieldByName(ColField).AsString);
CDS.Next;
end;
with aStringGriddo
begin
//填充单元格
RowCount:=RowString.Count+1;
ColCount:=ColString.Count+1;
for jCol:=0 to ColCount-1do
for iRow:=0 to RowCount-1do
Cells[jCol,iRow]:='';
SetLength(aStatus,ColCount,RowCount);
if (RowCount<=1) then
FixedRows:=0
else
FixedRows:=1;
if (ColCount<=1)then
FixedCols:=0
else
FixedCols:=1;
Cells[0,0] := CrossTabDesc;
//填充行
for i:=0 to RowString.Count-1do
Cells[0,i+1]:=RowString;
//填充列
for i:=0 to ColString.Count-1do
Cells[i+1,0]:=ColString;
//填充值
CDS.First;
for i:=0 to CDS.RecordCount-1do
begin
RowName:=CDS.fieldbyname(RowField).AsString;
ColName:=CDS.fieldbyname(ColField).AsString;
Status:=CDS.fieldbyname(StatusField).AsString;
for iRow:=0 to RowCount-1do
if Cells[0,iRow]=RowName then
break;
for jCol:=0 to ColCount-1do
if Cells[jCol,0]=ColName then
break;
aStatus[jCol,iRow]:=Status;
Cells[jCol,iRow]:=CDS.FieldByName(ValueField).AsString;
CDS.Next;
end;
end;
finally
RowString.Free;
ColString.Free;
end;
end;

hotboys的動態交叉表
http://www.cnblogs.com/bonny.wong/archive/2005/01/23/96124.html

谢谢,我不是用ms sql,不支持动态输出不定数量的字段
interbase存储过程需要先定义好输出字段,所以,无法在数据库一级实现

ColumnStr := TStringList.Create;
ParallColumnStr := TStringList.Create;
ColumnStr.Add('FSize');
//把Size進行旋轉，其對應的值為Qty
ParallColumnStr.Add('FQty');
dtWhirlData := WhirlDataSet(dtQuery, ColumnStr, ParallColumnStr, 0);
//最後一下參數表示以前幾列作為分組

Function WhirlDataSet(DtSet: TAdoDataSet;
ColumnToRow, ParallelismRowValue: TStrings;
iFrontGrp: Integer): TAdoDataSet;
var
i: Integer;
DtGenTmp: TAdoDataSet;
OldFixFlds: TStrings;
NwFldsName: TStrings;
NwFldsType: TStrings;
NwFldsLen: TStrings;
FieldType: TFieldType;
iColumn, iRow: integer;
LastValue: TStrings;
WillNewGrp: Boolean;
iNewFrontGrp: Integer;
begin

//本函數未對ColumnToRow/ParallelismRowValue的字段是否存在於DtSet作判斷，所以在調用本函數前用戶自己處理
if ColumnToRow.Count <>
ParallelismRowValue.Count then

begin

if ColumnToRow.Count >
ParallelismRowValue.Count then

begin

for i := ColumnToRow.Count - 1do
wnto ParallelismRowValue.Count - 1do

begin

ColumnToRow.Delete(i);
end;

end else

begin

for i := ParallelismRowValue.Count - 1do
wnto ColumnToRow.Count - 1do

begin

ParallelismRowValue.Delete(i);
end;

end;

end;


if (ColumnToRow.Count = 0) or (ParallelismRowValue.Count = 0) then

begin

Result := DtSet;
Exit;
end;


OldFixFlds := TStringList.Create;
NwFldsName := TStringList.Create;
NwFldsType := TStringList.Create;
NwFldsLen := TStringList.Create;

for i := 0 to DtSet.FieldCount - 1do
//DtSet不需要轉置的列
begin

iColumn := ColumnToRow.IndexOf(Trim(DtSet.Fields.FieldName));
iRow := ParallelismRowValue.IndexOf(Trim(DtSet.Fields.FieldName));
if (iColumn = -1) and (iRow = -1) then

begin

NwFldsName.Add(DtSet.Fields.FieldName);
OldFixFlds.Add(DtSet.Fields.FieldName);
NwFldsType.Add(IntToStr(Ord(dtSet.Fields.DataType)));
NwFldsLen.Add(IntToStr(dtSet.Fields.Size));
end;

end;


for i := 0 to ColumnToRow.Count - 1do
//DtSet需要轉置的列,並把值置為列名,而ParallelismRowValue則對應為值
begin

DtSet.First;
while not DtSet.Eofdo

begin

if NwFldsName.IndexOf(Trim(DtSet.FieldByName(ColumnToRow).AsString)) = -1 then

begin

NwFldsName.Add(Trim(DtSet.FieldByName(ColumnToRow).AsString));
NwFldsType.Add(IntToStr(Ord(DtSet.FieldByName(ParallelismRowValue).DataType)));
NwFldsLen.Add(IntToStr(DtSet.FieldByName(ParallelismRowValue).Size));
end;

DtSet.Next;
end;

end;


DtGenTmp := TAdoDataSet.Create(nil);
for i := 0 to NwFldsName.Count - 1do

begin

with DtGenTmp.FieldDefs.AddFieldDefdo

begin

Name := NwFldsName;
FieldType := TFieldType(StrToInt(NwFldsType));
DataType := FieldType;
if FieldType in [ftString, ftWideString, ftMemo] then
//這裡有Bug:主要原因是ftInteger的長度不能設置,其它數據類型也不知道要不要設定長度
size := StrToInt(NwFldsLen);
end;

end;

DtGenTmp.CreateDataSet;

LastValue := TStringList.Create;

dtSet.First;

if (OldFixFlds.Count - 1) <
iFrontGrp - 1 then

begin

iNewFrontGrp := 0;
end else

iNewFrontGrp := iFrontGrp - 1;

if not dtSet.IsEmpty then

begin

for i := 0 to iNewFrontGrpdo

begin

LastValue.Add('');
end;

end;


while not dtSet.Eofdo

begin

DtGenTmp.Append;

WillNewGrp := False;

for i := 0 to iNewFrontGrpdo

begin

if LastValue <>
dtSet.FieldByName(OldFixFlds).AsString then

begin

WillNewGrp := True;
Break;
end;

end;


if WillNewGrp then

LastValue.Clear;

for i := 0 to OldFixFlds.Count - 1do
//未轉置部分數據
begin

if WillNewGrp and (i <= iNewFrontGrp) then

begin

DtGenTmp.FieldByName(OldFixFlds).Value := dtSet.FieldByName(OldFixFlds).Value;
LastValue.Add(dtSet.FieldByName(OldFixFlds).AsString);
end else

if i >
iNewFrontGrp then

DtGenTmp.FieldByName(OldFixFlds).Value := dtSet.FieldByName(OldFixFlds).Value;
end;


for i := 0 to ColumnToRow.Count - 1do
//轉置部分數據
begin

DtGenTmp.FieldByName(Trim(dtSet.FieldByName(ColumnToRow).Value)).Value := dtSet.FieldByName(ParallelismRowValue).Value;
end;

DtGenTmp.Post;
dtSet.Next;
end;


DtGenTmp.First;

FreeAndNil(OldFixFlds);
FreeAndNil(NwFldsName);
FreeAndNil(NwFldsType);
FreeAndNil(NwFldsLen);
FreeAndNil(LastValue);

Result := DtGenTmp;
end;

谢谢hiyaolee，明天我认真看一下

在oracle下测试通过，没装intebase
------------------------------------------------------------------------------
select w.l,w.m one,q.m two from (select row_column l,
decode(col_column,1,value,null) m from a) w,(select row_column l,
decode(col_column,2,value,null) m from a) q where w.m is not null
and q.m is not null and w.l=q.l
/
------------------------------------------------------------------------------
SQL>
select * from a;
COL_COLUMN ROW_COLUMN VALUE
---------- -------------------- --------------------
1 a v1
2 a v2
1 b v3
2 b v4
1 c v5
2 c v6
-------------
SQL>
edit
已写入文件 afiedt.buf
1* select w.l,w.m one,q.m two from (select row_column l,decode(col_column,1,va
lue,null) m from a) w,(select row_column l,decode(col_column,2,value,null) m fro
m a) q where w.m is not null and q.m is not null and w.l=q.l
SQL>
/
L ONE TWO
-------------------- -------------------- --------------------
a v1 v2
b v3 v4
c v5 v6
------------
如果不行的话就用下面的，使用SQL99标准中的case when，各个数据库都支持的
-------------------------------------------------------------------------------
select w.l,w.m one,q.m two from (select row_column l,
(case col_column when 1 then
value else
null end) m from a) w,
(select row_column l,(case col_column when 2 then
value else
null end) m
from a) q where w.m is not null and q.m is not null and w.l=q.l
/
-------------------------------------------------------------------------------
SQL>
edit
已写入文件 afiedt.buf
1 select w.l,w.m one,q.m two from (select row_column l,
2 (case col_column when 1 then
value else
null end) m from a) w,
3 (select row_column l,(case col_column when 2 then
value else
null end) m
4* from a) q where w.m is not null and q.m is not null and w.l=q.l
SQL>
/
L ONE TWO
-------------------- -------------------- --------------------
a v1 v2
b v3 v4
c v5 v6
但我不知道不知道其它如别名，链接表intebase是否支持：（

今天经过高人指点，这个SQL可以这样写。。。
select row_column,max(decode(col_column,1,value,null)),max(decode(col_column,2,value,null)) from a group by row_column;
select row_column,max(case col_column when 1 then
value else
null end),max(case col_column when 2 then
value else
null end) from a group by row_column

谢谢各位,
hiyaolee的算法已经详细看过,跟我原来的算法没有本质区别，效率不高
xinxinhg的写法属于静态交叉的写法，依赖于数据。
xiaolinj79提供了HotBoy的，在ms sql的动态算法
事实上，在Oracle下的动态算法，也可以找到资料
我希望获得Pascal下的最优算法
非常感谢各位，继续期待中

這裡有個看看吧。
http://dev.csdn.net/develop/article/20/20229.shtm

hotboys,提供的资源，要点在于填值部分：
if DataSource.DataSet.Locate(rowfield,rowval,[loPartialKey]) then
这部分算法，比起我的填值算法优秀
我是循环源表，对目标表（StringGrid）定位
而hotboys提供的例子，是循环目标表，对源表定位，在row,col字段有索引的情况下
效率会大大提高
知道如何改我的代码了
谢谢各位

多人接受答案了。