CODEWITHSUNDEEP
sqlsql-server
stoney78us
stoney78us

Reputation: 305

Creating a view table dynamically

I have 2 tables named Circuits and Detections The Circuits table contains records of all possible circuits. The Detections table contains records of detections of circuits in Circuits table. I want to create a view that contains a list of circuits and the counts of each type of dectections for each particular circuits. I came up with the followings code but it doesn't work

CREATE VIEW Result
AS

declare @circuitId INT
declare my_cursor Cursor for
SELECT circuitId from Circuits

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @circuitId

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @circuitId as CircuitId , 
(SELECT COUNT(*) AS AliveCount FROM dbo.Detections WHERE dbo.Detections.circuitId = @circuitId AND dbo.detections.health = 'Alive') AS Alive, 
(SELECT COUNT(*) AS DeadCount FROM dbo.Detections WHERE dbo.Detections.circuitId = @circuitId AND       dbo.detections.health = 'Dead') AS Dead
FETCH NEXT FROM my_cursor into @circuitId
END
CLOSE my_cursor
DEALLOCATE my_cursor

I don't know if it is possible to use view in this case. Any suggestions are very appreciated. Thank you.

Upvotes: 0

Views: 50

Answers (2)

ekad
ekad

Reputation: 14614

Besides using SUM as suggested in @Tab Alleman's answer, you can also use COUNT as below

CREATE VIEW Result
AS

SELECT
    c.circuitId,
    COUNT(CASE WHEN d.health = 'Alive' THEN 'Alive' ELSE NULL END) AS Alive,
    COUNT(CASE WHEN d.health = 'Dead' THEN 'Dead' ELSE NULL END) AS Dead
FROM Circuits c
LEFT JOIN Detections d
    ON d.circuitId = c.circuitId
GROUP BY c.circuitId

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

No you can't use a cursor in a view. But I think you could do this:

CREATE VIEW Result
AS

SELECT 
  c.circuitId
, SUM(CASE WHEN d.health='Alive' THEN 1 ELSE 0 END) AS Alive
, SUM(CASE WHEN d.health='Dead' THEN 1 ELSE 0 END) AS Dead
FROM Circuits c
LEFT OUTER JOIN dbo.Detections d
  ON d.circuitId=c.circuitId
GROUP BY c.circuitId

Upvotes: 2

Related Questions