[交流]DELPHI中对SQL SERVER转置矩阵的处理

Question

procedure GetMatrixDataSet(DataSet :TADOQuery; TableName, KeyField,
  DisplayField, sumField:string); //获取转置矩阵的结果集
//对于原始数据集进行转置。例如：
// FieldA FieldB FieldC
// a      AA     1
// b      AA     2
// a      BB     3
// b      BB     4
// b      BB     5
//使用GetMatrixDataSet(TableName, 'FieldA', 'FieldB', 'FieldC') 转置后的结果：
// FieldA AA BB
// a      1  3
// b      2  9
// 注意：KeyField、DisplayField应为字符类型，SumField应为数字类型。
// 如果有什么特殊要求可以进行修改。
var
  i:integer;
  s:string;
  sl:TStringList; //保存显示的列名
begin
  sl:= TStringList.Create;  s := KeyField;
  with DataSet do
  try
    DisableControls;
    Close;
    SQL.Text := Format('SELECT DISTINCT %s FROM %s ORDER BY %0:s', [DisplayField, TableName]);
    Open;
    for i:=0 to RecordCount-1 do
    begin
      if VarIsNull(FieldValues[DisplayField]) then
      begin
        sl.Add('<NULL>');
        s := s + Format(', SUM(CASE WHEN %s IS NULL THEN IsNull(%s,0) ELSE 0 END)',
          [DisplayField, SumField])
      end
      else begin
        s := s + Format(', SUM(CASE WHEN %s = ''%s'' THEN IsNull(%s,0) ELSE 0 END)',
          [DisplayField, VarToStr(FieldValues[DisplayField]), SumField]);        if (VarToStr(FieldValues[DisplayField]) = '') then
          sl.Add(' ')
        else
          sl.Add(VarToStr(FieldValues[DisplayField]));
      end;
      Next;
    end;
    Close;
    SQL.Text := Format('SELECT %s FROM %s GROUP BY %s', [s, TableName, KeyField]);
    Open;    for i:=1 to FieldCount-1 do
    begin
      Fields[i].DisplayLabel := sl[i-1];
    end;
    EnableControls;
  except
  end;
end;

Answer 1

呵...有用的东西...
我以前做只是在SQL服务端做类似处理.
希望更多的人明白这些思路方向.

Answer 2

在服务器端处理不是不可以。
我原来就是打算用一个存储过程来做的。
但是为了灵活地操作，我感觉必须使用游标，太麻烦，不如用DELPHI处理简单。
由于象这种转置的输出结果不会很多，效率上应该没有影响。

Answer 3

在服务器端处理不是不可以。
我原来就是打算用一个存储过程来做的。
但是为了灵活地操作，我感觉必须使用游标，太麻烦，不如用DELPHI处理简单。
由于象这种转置的输出结果不会很多，效率上应该没有影响。

Answer 4

windindance(风舞轻扬),可能你说的同我的意思不一样.
使用单位阵的方法是不要游标的.
查询也只要一个select就可以返回的...
只是要针对每种情况具体写.我主要是考虑它怎么重用..

Answer 5

halfdream(哈欠):
你是怎样做的呢，能否贴出来看看？

Answer 6

假设数据表是...
TableA
// FieldA FieldB FieldC
------------------------
// a      AA     1
// b      AA     2
// a      BB     3
// b      BB     4
// b      BB     5
单位阵表:
TableMName  Value1,Value2,Value3
---------------------        
AA       1,     0,      0
BB       0, 1, 0
CC       0, 0, 1然后....
SELECT b.FieldA as FieldA, 
      SUM(b.FieldC * a.Value1) AS AA, 
      SUM(b.FieldC * a.Value2) AS BB
FROM tableM a ,tableA b 
where a.Name = b.FieldB
GROUP BY b.FieldA使用这样的查询就可以得到的结果..//--------------------
FieldA AA BB
 a      1  3
 b      2  9
//----------------------------
//它具体理论基础是线程代数矩阵相乘..
//我仍希望这种方法能够更自动化一些:)
//---------------------------------

Answer 7

一般不是很经常用。但是决策分析的时候会用到这个功能。
可以看看帖子http://www.csdn.net/expert/topic/1015/1015029.xml?temp=.8315241
或者http://www.csdn.net/expert/topic/1007/1007781.xml?temp=.2357752
都是类似的问题。

Answer 8

其实可以参考著名的FR的CROSS报表的算法。

Answer 9

realonedot(小点):
我不熟悉，能否把算法贴出来看看？

Answer 10

如果是： yyb2000(三流编程机器) 所说的用途的话，使用TDecisionCube就可以实现了

Answer 11

仅限于SQL Server 2000:
我觉得不妨使用自定义函数，如CREATE FUNCTION FieldValue (
参数列表)
RETURNS int  AS  
BEGIN 
DECLARE @RET int
select @ret=计算值 from 表 where 条件1=参数1 and .....
RETURN @RET
END在存储过程中或其他地方使用
create procedure ...
...
...
select .... fieldA,FieldValue(fieldA,'AA') as AA,FieldValue(fieldA,'BB') as BB....
from 表
...这样处理不就行了？大不了拼串

Answer 12

补充
FieldValue(fieldA,'AA')中'AA'可以通过其他方式获得
充分利用函数可以很好地解决此问题

Answer 13

fredfei(飞飞)：
呵呵，还得拼串哦：）
好像的确没有太好的办法，最终都要到拼串上来。

Answer 14

矩阵转置方法，代码！type
  TMatrix = array of array of Extended;  ENotSquare = class(Exception);
  EDimNotMatch = class(Exception);
...procedure LDLT(m: TMatrix; var l, d: TMatrix);  //LDL'分解
var
  n, i, j, k: Integer;
  s: Extended;
begin
  n := High(m);
  if High(m[0]) <> n then
    Raise ENotSquare.Create('LDL''分解需要方阵！');
  SetLength(l, n + 1, n + 1);
  SetLength(d, n + 1, n + 1);
  for i := 0 to n do
    for j := 0 to n do begin
      l[i, j] := Ord(i = j);
      d[i, j] := 0;
    end;
  for i := 0 to n do begin
    s := m[i, i];
    for j := 0 to i - 1 do
      s := s - Sqr(l[i, j]) * d[j, j];
    d[i, i] := s;
    for k := i + 1 to n do begin
      s := m[k, i];
      for j := 0 to i - 1 do
        s := s - l[k, j] * l[i, j] * d[j, j];
      l[k, i] := s / d[i, i];
    end;
  end;
end;procedure Transpose(m: TMatrix; var Trans: TMatrix);  //矩阵转置
var
  i, j, n0, n1: Integer;
begin
  n0 := High(m);
  n1 := High(m[0]);
  SetLength(Trans, n1 + 1, n0 + 1);
  for i := 0 to n0 do
    for j := 0 to n1 do
      Trans[j, i] := m[i, j];
end;procedure Multiply(m1, m2: TMatrix; var Res: TMatrix);  //矩阵乘法
var
  i, j, k, n0, n1, n2: Integer;
  s: Extended;
begin
  n0 := High(m1);
  n1 := High(m1[0]);
  if n1 <> High(m2) then
    Raise EDimNotMatch.Create('矩阵维数不匹配！');
  n2 := High(m2[0]);
  SetLength(Res, n0 + 1, n2 + 1);
  for i := 0 to n0 do
    for j := 0 to n2 do begin
      s := 0;
      for k := 0 to n1 do
        s := s + m1[i, k] * m2[k, j];
      Res[i, j] := s;
    end;
end;procedure InverseSymmetry(m: TMatrix; var Inv: TMatrix);  //对称阵求逆
var
  l, d, lt, temp: TMatrix;
  i, j, k, n: Integer;
  s: Extended;
begin
  n := High(m);
  LDLT(m ,l, d);
  SetLength(lt, n + 1, n + 1);
  for i := 0 to n do
    for j := 0 to n do
      lt[i, j] := Ord(i = j);
  for i := 0 to n do begin
    d[i, i] := 1 / d[i, i];
    for j := i + 1 to n do begin
      s := 0;
      for k := 0 to j - 1 do
        s := s + l[j, k] * lt[k, i]; 
      lt[j, i] := -s;
    end;
  end;
  Transpose(lt, l);
  Multiply(l, d, temp);
  Multiply(temp, lt, Inv);
end;