TSQL syntax needed to count values based on grouping

Hi,

I need to count the number of occurrences of Zones A, B and C in the temp table as seen in the code below.

The tsql first groups instances where the same [NAME] and [Type] column values occur consecutively with a factor of 3. Then I need to count the number of A,B & C zones, the expected outcome is:

--Required outcome

--NAME	Type	Count	ZoneA	ZoneB	ZoneC
------------------------------------------------
--Name1	Type1	3	2	1	0
--Name1	Type2	3	1	0	2
--Name2	Type1	3	1	2	0

Select allOpen in new window

DECLARE @TempTable TABLE
([Id] INT
,[Name] VARCHAR(20)
,[Type] VARCHAR(10)
,[Zone] VARCHAR(10)
)

INSERT INTO @TempTable
VALUES(1,'Name1','Type2','A')
,(2,'Name1','Type1','B')
,(3,'Name1','Type1','A')
,(4,'Name1','Type1','A')
,(5,'Name1','Type2','C')
,(6,'Name1','Type2','C')
,(7,'Name1','Type2','A')
,(8,'Name2','Type1','A')
,(9,'Name2','Type1','B')
,(10,'Name2','Type1','B')

--SELECT * FROM @TempTable

;with c as (
    select 
       [NAME], [Type], [Zone],
       row_number() over (partition by [NAME] order by [NAME], [Id] ASC) 
       - row_number() over (partition by [NAME], [Type] order by [NAME], [Id] ASC) grp
from (SELECT TOP 100 PERCENT *
FROM @TempTable
ORDER BY [NAME], [Id] ASC
)T1
)

SELECT [NAME],[Type],count([Type]) [Count]
FROM c
GROUP BY [NAME],[Type], grp
HAVING count([Type]) = 3

Select allOpen in new window

Thank you