Another way to get the result is by a recursive CTE

    WITH TeamRes As (
    SELECT FT.Team_ID
         , FM.match_id
         , Previous_Match = LAG(match_id, 1, 0) 
                            OVER (PARTITION BY FT.Team_ID ORDER BY FM.match_id)
         , Matches = Row_Number() 
                     OVER (PARTITION BY FT.Team_ID ORDER BY FM.match_id Desc)
         , Result = Case Coalesce(winning_team_id, -1)
                         When -1 Then 'T'
                         When FT.Team_ID Then 'W'
                         Else 'L'
                    End 
    FROM   FantasyMatches FM
           INNER JOIN FantasyTeams FT ON FT.Team_ID IN 
             (FM.home_fantasy_team_id, FM.away_fantasy_team_id)
    ), Streaks AS (
    SELECT Team_ID, Result, 1 As Streak, Previous_Match
    FROM   TeamRes
    WHERE  Matches = 1
    UNION ALL
    SELECT tr.Team_ID, tr.Result, Streak + 1, tr.Previous_Match
    FROM   TeamRes tr
           INNER JOIN Streaks s ON tr.Team_ID = s.Team_ID 
                               AND tr.Match_id = s.Previous_Match 
                               AND tr.Result = s.Result
    )
    Select Team_ID, Result, Max(Streak) Streak
    From   Streaks
    Group By Team_ID, Result
    Order By Team_ID


[SQLFiddle][1] demo


  [1]: http://www.sqlfiddle.com/#!6/c01c5/18

One intuitive approach to solve this problem is:

1. Find the most recent result for each team
2. Check the previous match and add one to the streak count if the result type matches
3. Repeat step 2 but stop as soon as the first different result is encountered

This strategy might win out over the window function solution (which performs a full scan of the data) as the table grows larger, assuming the recursive strategy is implemented efficiently. The key to success is to provide efficient indexes to locate rows quickly (using seeks) and to avoid sorts. The indexes needed are:

    -- New index #1
    CREATE UNIQUE INDEX uq1 ON dbo.FantasyMatches 
        (home_fantasy_team_id, match_id) 
    INCLUDE (winning_team_id);
    
    -- New index #2
    CREATE UNIQUE INDEX uq2 ON dbo.FantasyMatches 
        (away_fantasy_team_id, match_id) 
    INCLUDE (winning_team_id);

To assist in query optimization, I will use a temporary table to hold rows identified as forming part of a current streak. If streaks are typically short (as is true for the teams I follow, sadly) this table should be quite small:

    -- Table to hold just the rows that form streaks
    CREATE TABLE #StreakData
    (
        team_id bigint NOT NULL,
        match_id bigint NOT NULL,
        streak_type char(1) NOT NULL,
        streak_length integer NOT NULL,
    );
    
    -- Temporary table unique clustered index
    CREATE UNIQUE CLUSTERED INDEX cuq ON #StreakData (team_id, match_id);

My recursive query solution is as follows ([**SQL Fiddle here**][1]):

    -- Solution query
    WITH Streaks AS
    (
        -- Anchor: most recent match for each team
        SELECT 
            FT.team_id, 
            CA.match_id, 
            CA.streak_type, 
            streak_length = 1
        FROM dbo.FantasyTeams AS FT
        CROSS APPLY
        (
            -- Most recent match
            SELECT
                T.match_id,
                T.streak_type
            FROM 
            (
                SELECT 
                    FM.match_id, 
                    streak_type =
                        CASE 
                            WHEN FM.winning_team_id = FM.home_fantasy_team_id
                                THEN CONVERT(char(1), 'W')
                            WHEN FM.winning_team_id IS NULL
                                THEN CONVERT(char(1), 'T')
                            ELSE CONVERT(char(1), 'L')
                        END
                FROM dbo.FantasyMatches AS FM
                WHERE 
                    FT.team_id = FM.home_fantasy_team_id
                UNION ALL
                SELECT 
                    FM.match_id, 
                    streak_type =
                        CASE 
                            WHEN FM.winning_team_id = FM.away_fantasy_team_id
                                THEN CONVERT(char(1), 'W')
                            WHEN FM.winning_team_id IS NULL
                                THEN CONVERT(char(1), 'T')
                            ELSE CONVERT(char(1), 'L')
                        END
                FROM dbo.FantasyMatches AS FM
                WHERE
                    FT.team_id = FM.away_fantasy_team_id
            ) AS T
            ORDER BY 
                T.match_id DESC
                OFFSET 0 ROWS 
                FETCH FIRST 1 ROW ONLY
        ) AS CA
        UNION ALL
        -- Recursive part: prior match with the same streak type
        SELECT 
            Streaks.team_id, 
            LastMatch.match_id, 
            Streaks.streak_type, 
            Streaks.streak_length + 1
        FROM Streaks
        CROSS APPLY
        (
            -- Most recent prior match
            SELECT 
                Numbered.match_id, 
                Numbered.winning_team_id, 
                Numbered.team_id
            FROM
            (
                -- Assign a row number
                SELECT
                    PreviousMatches.match_id,
                    PreviousMatches.winning_team_id,
                    PreviousMatches.team_id, 
                    rn = ROW_NUMBER() OVER (
                        ORDER BY PreviousMatches.match_id DESC)
                FROM
                (
                    -- Prior match as home or away team
                    SELECT 
                        FM.match_id, 
                        FM.winning_team_id, 
                        team_id = FM.home_fantasy_team_id
                    FROM dbo.FantasyMatches AS FM
                    WHERE 
                        FM.home_fantasy_team_id = Streaks.team_id
                        AND FM.match_id < Streaks.match_id
                    UNION ALL
                    SELECT 
                        FM.match_id, 
                        FM.winning_team_id, 
                        team_id = FM.away_fantasy_team_id
                    FROM dbo.FantasyMatches AS FM
                    WHERE 
                        FM.away_fantasy_team_id = Streaks.team_id
                        AND FM.match_id < Streaks.match_id
                ) AS PreviousMatches
            ) AS Numbered
            -- Most recent
            WHERE 
                Numbered.rn = 1
        ) AS LastMatch
        -- Check the streak type matches
        WHERE EXISTS
        (
            SELECT 
                Streaks.streak_type
            INTERSECT
            SELECT 
                CASE 
                    WHEN LastMatch.winning_team_id IS NULL THEN 'T' 
                    WHEN LastMatch.winning_team_id = LastMatch.team_id THEN 'W' 
                    ELSE 'L' 
                END
        )
    )
    INSERT #StreakData
        (team_id, match_id, streak_type, streak_length)
    SELECT
        team_id,
        match_id,
        streak_type,
        streak_length
    FROM Streaks
    OPTION (MAXRECURSION 0);

The T-SQL text is quite long, but each section of the query corresponds closely to the broad process outline given at the start of this answer. The query is made longer by the need to use certain tricks to avoids sorts and to produce a `TOP` in the recursive part of the query (which is normally not allowed).

The execution plan is relatively small and simple by comparison with the query. I have shaded the anchor region yellow, and the recursive part green in the screenshot below:

![Recursive execution plan][2]

With the streak rows captured in a temporary table, it is easy to get the summary results you require. (Using a temporary table also avoids a sorting spill that might occur if the query below were combined with the main recursive query)

    -- Basic results
    SELECT
        SD.team_id,
        StreakType = MAX(SD.streak_type),
        StreakLength = MAX(SD.streak_length)
    FROM #StreakData AS SD
    GROUP BY 
        SD.team_id
    ORDER BY
        SD.team_id;

![Basic query execution plan][3]

The same query can be used as a basis for updating the `FantasyTeams` table:

    -- Update team summary
    WITH StreakData AS
    (
        SELECT
            SD.team_id,
            StreakType = MAX(SD.streak_type),
            StreakLength = MAX(SD.streak_length)
        FROM #StreakData AS SD
        GROUP BY 
            SD.team_id
    )
    UPDATE FT
    SET streak_type = SD.StreakType,
        streak_count = SD.StreakLength
    FROM StreakData AS SD
    JOIN dbo.FantasyTeams AS FT
        ON FT.team_id = SD.team_id;

Or, if you prefer `MERGE`:

    MERGE dbo.FantasyTeams AS FT
    USING
    (
        SELECT
            SD.team_id,
            StreakType = MAX(SD.streak_type),
            StreakLength = MAX(SD.streak_length)
        FROM #StreakData AS SD
        GROUP BY 
            SD.team_id
    ) AS StreakData
        ON StreakData.team_id = FT.team_id
    WHEN MATCHED THEN UPDATE SET
        FT.streak_type = StreakData.StreakType,
        FT.streak_count = StreakData.StreakLength;

Either approach produces an efficient execution plan (based on the known number of rows in the temporary table):

![Update execution plan][4]

Finally, because the recursive method naturally includes the `match_id` in its processing, it is easy to add a list of the `match_id`s that form each streak to the output:

    SELECT
        S.team_id,
        streak_type = MAX(S.streak_type),
        match_id_list =
            STUFF(
            (
                SELECT ',' + CONVERT(varchar(11), S2.match_id)
                FROM #StreakData AS S2
                WHERE S2.team_id = S.team_id
                ORDER BY S2.match_id DESC
                FOR XML PATH ('')
            ), 1, 1, ''),
        streak_length = MAX(S.streak_length)
    FROM #StreakData AS S
    GROUP BY 
        S.team_id
    ORDER BY
        S.team_id;

Output:

![Match list included][5]

Execution plan:

![Match list execution plan][6]

  [1]: http://sqlfiddle.com/#!6/92e8e/1
  [2]: https://i.stack.imgur.com/l7kwJ.png
  [3]: https://i.stack.imgur.com/QhP4a.png
  [4]: https://i.stack.imgur.com/JaTWR.png
  [5]: https://i.stack.imgur.com/n3gvs.png
  [6]: https://i.stack.imgur.com/ayeIN.png

Since you are on SQL Server 2012 you can use a couple of the new windowing functions.

    with C1 as
    (
      select T.team_id,
             case
               when M.winning_team_id is null then 'T'
               when M.winning_team_id = T.team_id then 'W'
               else 'L'
             end as streak_type,
             M.match_id
      from FantasyMatches as M
        cross apply (values(M.home_fantasy_team_id),
                           (M.away_fantasy_team_id)) as T(team_id)
    ), C2 as
    (
      select C1.team_id,
             C1.streak_type,
             C1.match_id,
             lag(C1.streak_type, 1, C1.streak_type) 
               over(partition by C1.team_id 
                    order by C1.match_id desc) as lag_streak_type
      from C1
    ), C3 as
    (
      select C2.team_id,
             C2.streak_type,
             sum(case when C2.lag_streak_type = C2.streak_type then 0 else 1 end) 
               over(partition by C2.team_id 
                    order by C2.match_id desc rows unbounded preceding) as streak_sum
      from C2
    )
    select C3.team_id,
           C3.streak_type,
           count(*) as streak_count
    from C3
    where C3.streak_sum = 0
    group by C3.team_id,
             C3.streak_type
    order by C3.team_id;

[SQL Fiddle](http://www.sqlfiddle.com/#!6/c01c5/26)


`C1` calculates the `streak_type` for each team and match.

`C2` finds the previous `streak_type` ordered by `match_id desc`.

`C3` generates a running sum `streak_sum` ordered by `match_id desc` keeping a `0` a long as the `streak_type` is the same as the last value.

Main query sums up the streaks where `streak_sum` is `0`.