创建动态数组

Question

我有一个表名Tankdet，它有两个列TCO和Tanks。

以下是用于返回租赁、拥有和主坦克数量的存储过程代码：

ALTER PROCEDURE [dbo].[sp_Dash_LeaseInformation]  
AS  
BEGIN  
 DECLARE @leased INT  
 DECLARE @owned INT
 DECLARE @principal INT
  
 SET NOCOUNT ON  
  
 SET @owned = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='owned')  
 SET @leased = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='leased')   
 SET @principal = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='principle') 
 SELECT [Leased]=@leased,[Owned]=@owned,[Principal]=@principal 
  
    SET NOCOUNT OFF  
END  

这张桌子看起来像这样

​

​

它返回的值如下，

leased = 4,owned = 4,principle = 7

用于显示我前面的坦克数量的值。

问题是，在这里，我展示了全部的主要坦克，并返回到我的前面。

现在，在为每个Principle Tank单独获取TCO计数时，我遇到了创建数组的问题，我必须将其返回如下：

SELECT [Leased]=@leased, [Owned]=@owned, [vibe]=@vibe, [baru]=@baru,[sarath]=@sarath, [karthi]=@karthi, [nth...]=@nth.....

nth是因为TCO值将来可能出于目的而被添加，而selected值必须返回如下：

leased=4，owned=4，vibe=2，baru=3，sarath=1，karthi=1，nth= n.

Answer 1

表(和结果集)有固定的列数和可变的行数。所以简单的返回

SELECT ownleasetank, COUNT(*) TankCount
FROM Tankdet 
GROUP BY ownleasetank

它将返回每一个ownleasetank值一行以及计数。