2 Two examples for completeness.
source | link

Alternative if you're mildly allergic to correlated subqueries (Erik Darling's answer) and CTEs (kevinnwhat's answer) like me.

Be aware that when nulls are thrown in to the mix, none of these may work how you would like them to. (but it's fairly simple to modify them to taste)

Simple case:

--ignore the existence of nulls
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT [Col_A], COUNT(DISTINCT [Col_B]) AS [Distinct_B]
    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
;

Same as above, but with comments on what to change for null handling:

--customizable null handling
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT 

    [Col_A],

    (
        COUNT(DISTINCT [Col_B])
        /*
        --uncomment if you also want to count Col_B NULL
        --as a distinct value
        +
        MAX(
            CASE
                WHEN [Col_B] IS NULL
                THEN 1
                ELSE 0
            END
        )
        */
    )
    AS [Distinct_B]

    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
/*
--uncomment if you also want to include Col_A when it's NULL
OR
([mt].[Col_A] IS NULL AND [Distinct_B].[Col_A] IS NULL)
*/

Alternative if you're mildly allergic to correlated subqueries (Erik Darling's answer) and CTEs (kevinnwhat's answer) like me.

Be aware that when nulls are thrown in to the mix, none of these may work how you would like them to. (but it's fairly simple to modify them to taste)

SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT [Col_A], COUNT(DISTINCT [Col_B]) AS [Distinct_B]
    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
/*
--uncomment if you also want to count Col_A when it's NULL
OR
([mt].[Col_A] IS NULL AND [Distinct_B].[Col_A] IS NULL)
*/

Alternative if you're mildly allergic to correlated subqueries (Erik Darling's answer) and CTEs (kevinnwhat's answer) like me.

Be aware that when nulls are thrown in to the mix, none of these may work how you would like them to. (but it's fairly simple to modify them to taste)

Simple case:

--ignore the existence of nulls
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT [Col_A], COUNT(DISTINCT [Col_B]) AS [Distinct_B]
    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
;

Same as above, but with comments on what to change for null handling:

--customizable null handling
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT 

    [Col_A],

    (
        COUNT(DISTINCT [Col_B])
        /*
        --uncomment if you also want to count Col_B NULL
        --as a distinct value
        +
        MAX(
            CASE
                WHEN [Col_B] IS NULL
                THEN 1
                ELSE 0
            END
        )
        */
    )
    AS [Distinct_B]

    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
/*
--uncomment if you also want to include Col_A when it's NULL
OR
([mt].[Col_A] IS NULL AND [Distinct_B].[Col_A] IS NULL)
*/
1
source | link

Alternative if you're mildly allergic to correlated subqueries (Erik Darling's answer) and CTEs (kevinnwhat's answer) like me.

Be aware that when nulls are thrown in to the mix, none of these may work how you would like them to. (but it's fairly simple to modify them to taste)

SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT [Col_A], COUNT(DISTINCT [Col_B]) AS [Distinct_B]
    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
/*
--uncomment if you also want to count Col_A when it's NULL
OR
([mt].[Col_A] IS NULL AND [Distinct_B].[Col_A] IS NULL)
*/