Create Dynamic Array

Question

I have a table name Tankdet which has two columns TCO and Tanks.

Here is the stored procedure code to return the count of leased, owned and principle 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

The table looks like this

and it returns the values like,

leased = 4,owned = 4,principle = 7

The values which is used to show the tank count in my frontend.

The problem is here I display the whole count of principle tanks and returns it to my frontend.

Now I have the problem of creating array in getting the Principle Tank count as separate for each TCO, and I have to return it as:

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

The nth is because the TCO values may get added in future for purpose, and the selected values have to return like:

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

Side note, the sp_ prefix is reserved by Microsoft and means Special Procedure. It should not be used as a prefix for user objects. Is the sp_ prefix still a no-no? — Thom A
– Thom A ♦, Commented Jul 19, 2020 at 16:47
I have also removed the oracle tag, as this is clearly SQL Server (due to the dbo schema). Please don't tag random other RDBMS when asking questions. — Thom A
– Thom A ♦, Commented Jul 19, 2020 at 16:55
@Larnu yes i"m looking for dynamic pivot do you have any example to solve the problem — Vibeshnan Kanagamani
– Vibeshnan Kanagamani, Commented Jul 19, 2020 at 17:05
There are literally 100's on this site: Dynamic Pivot SQL Server — Thom A
– Thom A ♦, Commented Jul 19, 2020 at 17:06

David Browne - Microsoft · Accepted Answer · 2020-07-19 16:50:44Z

1

Tables (and resultsets) have a fixed number of columns and a variable number of rows. So simply return

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

That will return one row per ownleasetank value along with the count.

answered Jul 19, 2020 at 16:50

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Dynamic Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related