TSQL Group by Column with Multiple Values

Question

I have a table in SQLServer 2008r2 as below.

I want to select all the records where the [Fg] column = 1 that consecutively by [Id] order lead into value 2 for each [T_Id] and [N_Id] combination.

There can be instances where record prior to [Fg] = 2 doesn't = 1

There can be any number of records where the value of [Fg] = 1 but only one record where [Fg] = 2 for each [T_Id] and [N_Id] combination.

So for the example below, I want to select records with [Id]s (4,5) and (7,8,9 )and (19,20).

Any records for [T_Id] 3 and 4 are excluded.

Expected output

Example data set

DECLARE @Data TABLE ( Id INT IDENTITY (1,1), T_Id INT, N_Id INT, Fg TINYINT )

INSERT INTO @Data
(T_Id, N_Id, Fg)
VALUES
(1, 2, 0), (1, 2, 1), (1, 2, 0), (1, 2, 1), (1, 2, 2), (2, 3, 0), (2, 3, 1), 
(2, 3, 1), (2, 3, 2), (3, 4, 0), (3, 4, 0), (3, 4, 0), (3, 4, 2), (4, 5, 0), 
(4, 5, 1), (4, 5, 0), (4, 5, 2), (5, 7, 0), (5, 7, 1), (5, 7, 2) 

Answer 1

It can be done easily using recursive CTE:

WITH DataSource AS
(
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN @Data DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] + 1
        AND DS1.[Fg] = 2
        AND DS2.[Fg] = 1
    UNION ALL
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN DataSource DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] - 1
        AND DS1.[Fg] = 1
)
SELECT *
FROM DataSource
ORDER BY Id

The idea is simple. The first part of the query gets all valid records with fg = 2 - valid means there is record before this one with fg = 1 from the same group.

Then in the recursive part we are getting all records smaller then initial ones, that has fg = 1.