SQL：在一个存储过程中组合多个动态结果集

We're having a small issue and could use some help - we have the need to combine multiple resultsets from one stored procedure into one resultset (the limitation of our Java reporting framework). We've looked at Union, etc. but the problem is that the stored procedure resultsets are multiple crosstab results and the (1) number of columns for each resultset are unknown and (2) the column names for each resultset are unknown.

我们遇到了一个小问题并且可以使用一些帮助 - 我们需要将来自一个存储过程的多个结果集合并到一个结果集中(我们的Java报告框架的限制)。我们已经查看了Union等,但问题是存储过程结果集是多个交叉表结果,并且每个结果集的(1)列数是未知的,(2)每个结果集的列名是未知的。

Basically, if the sp_ has 3 results like:

基本上,如果sp_有3个结果,如:

ID Name

1 Sam

2 Time

ID FName LName

1 John Jacob

1约翰雅各布

2 Tim Test

2蒂姆测试

3 Sam Hopkins

ID Amount

1 1000

2 5000

The ideal result would basically return the above text as-is which our framework would print to the user. Also please note that these 3-4 resultsets are not related to each other.

理想的结果基本上会返回我们的框架将打印给用户的上述文本。另请注意,这3-4个结果集彼此无关。

We're using SQL Server 2000 and Java 1.4.

我们正在使用SQL Server 2000和Java 1.4。

Any advice would be appreciated.

任何意见,将不胜感激。

Thanks, SP

PS: An alternative explaination in case the one above is not very clear. In SQL Query Analyzer if we have 3 select statements:

PS:如果上面的一个不是很清楚的话,另一种解释。在SQL查询分析器中,如果我们有3个select语句:

select * from countries; {returns id,countryname,countrycode}

从国家/地区选择*; {return id,countryname,countrycode}

select * from people; {id,countryname,countrycode}

选择*来自人; {ID,COUNTRYNAME,COUNTRYCODE}

select * from balance; {id,countryname,countrycode}

从余额中选择*; {ID,COUNTRYNAME,COUNTRYCODE}

Then the results are displayed in three separate resultset boxes. We need to these resultsets to be returned as one resultset by the stored procedure (while not knowing the number/name of the columns due to the crosstab-ing taking place). Thanks.

然后结果显示在三个单独的结果集框中。我们需要将这些结果集作为一个结果集由存储过程返回(而不知道由于交叉交叉发生而导致的列的数量/名称)。谢谢。

2 个解决方案

#1

Your question does not specify which database vendor, or which client application framework you are using, but most databases with stored procs have the capability to return multiple resultsets, and the few client frameworks I am familiar with (VB6, C++, .Net, etc.) all have the capability to retrieve these multiple resultsets from a single database access and manipulate them to do just about whatewver you might want...

您的问题没有指定您正在使用哪个数据库供应商或哪个客户端应用程序框架,但是大多数具有存储过程的数据库都能够返回多个结果集,以及我熟悉的少数客户端框架(VB6,C ++,。Net等) 。)所有人都有能力从单个数据库访问中检索这些多个结果集并操纵它们来做你想做的事情......

based on your comment, if your reporting framework can be hard coded to generate the column headings (firstName, lastName, amount, etc) without getting these strings from the database, then you could do this:

根据您的评论,如果您的报告框架可以硬编码生成列标题(firstName,lastName,amount等)而不从数据库中获取这些字符串,那么您可以这样做:

  Select ID, Name as value1, null as value2
  From TableA 
    Union
  Select ID, FName as value1, LName as value2
  From TableB 
    Union
  Select ID, Cast(Amount as VarChar(20)) as value1, null as value2
  From TableC

The key is that the number of columns returned by each select must be the same (3 in this example) and their names (aliases) and datatypes must be the same as well...

关键是每个select返回的列数必须相同(本例中为3),它们的名称(别名)和数据类型必须相同...

#2

if the ids from the different tables are related, then your t-SQL should be left joins.

如果来自不同表的ID是相关的,那么你的t-SQL应该是左连接。

#1