Do While Not rsFind.EOF sValue = Trim(rsFind.Fields("StockTrust").Value & "") sName = Trim(rsFind.Fields("StockName").Value & "") sTotalString = sTotalString & ", Sum(CASE StockTrust WHEN '" & sValue & "' THEN Amount ELSE 0 END) AS " & sName & "数量 , Sum(CASE StockTrust WHEN '" & sValue & "' THEN cMoney ELSE 0 END) AS " & sName & "金额 " rsFind.MoveNext Loop
行,但是要用Olap,access的transform是不通用的 以前做过,现在不记得怎么写了:(
Select * from [connstr].access.tablename, SQLdbname.dbo.sqltblname
Select * from [connstr].accessdbname.tablename, SQLdbname.dbo.sqltblname
--呜呼!!累死我也,存储过程调试真麻烦!! --sqlserver里面有个示例数据库:Northwind,在里面建立存储过程,把以下代码贴进去,然后在查询分析器里运行通过, --自己的数据库里只要把相应的参数按照说明传进去就行了。 CREATE procedure CorssTab @strTabName as varchar(50) = 'Employees', --此处放表名 @strCol as varchar(50) = 'City', --表头分组依据字段 @strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段 @strNumber as varchar(50) = 'ReportsTo', --被统计的字段 @strSum as varchar(10) = 'Sum' --运算方式 ASDECLARE @strSql as varchar(1000), @strTmpCol as varchar(100) EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') begin SET nocount ON SET @strsql ='select ' + @strGroup OPEN corss_cursor while (0=0) BEGIN FETCH NEXT FROM corss_cursor INTO @strTmpCol if (@@fetch_status<>0) break SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS ' + @strTmpCol END SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup EXECUTE(@strsql) IF @@error <>0 RETURN @@error CLOSE corss_cursor DEALLOCATE corss_cursor RETURN 0 end GO
我在数据库版里看到有人解过,我试一下再贴出来
sValue = Trim(rsFind.Fields("StockTrust").Value & "")
sName = Trim(rsFind.Fields("StockName").Value & "")
sTotalString = sTotalString & ", Sum(CASE StockTrust WHEN '" & sValue & "' THEN Amount ELSE 0 END) AS " & sName & "数量 , Sum(CASE StockTrust WHEN '" & sValue & "' THEN cMoney ELSE 0 END) AS " & sName & "金额 "
rsFind.MoveNext
Loop
以前做过,现在不记得怎么写了:(
--sqlserver里面有个示例数据库:Northwind,在里面建立存储过程,把以下代码贴进去,然后在查询分析器里运行通过,
--自己的数据库里只要把相应的参数按照说明传进去就行了。
CREATE procedure CorssTab
@strTabName as varchar(50) = 'Employees', --此处放表名
@strCol as varchar(50) = 'City', --表头分组依据字段
@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段
@strNumber as varchar(50) = 'ReportsTo', --被统计的字段
@strSum as varchar(10) = 'Sum' --运算方式
ASDECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ')
begin
SET nocount ON
SET @strsql ='select ' + @strGroup OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT FROM corss_cursor
INTO @strTmpCol
if (@@fetch_status<>0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS ' + @strTmpCol
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup EXECUTE(@strsql) IF @@error <>0 RETURN @@error
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 end
GO