FizzBuzz

まずは FizzBuzz から。
思いついたままに書いただけなので色々とアレ。

WITH
  Input(n) AS (SELECT 100)
, FizzBuzz(n, fizz, buzz, fizzbuzz, max_n) AS (
    SELECT
        1
      , CAST(NULL AS varchar(max))
      , CAST(NULL AS varchar(max))
      , CAST(NULL AS varchar(max))
      , n
    FROM
        Input
    UNION ALL
    SELECT
        n + 1
      , CAST(CASE WHEN (n + 1) % 3 = 0 THEN 'Fizz' ELSE NULL END AS varchar(max))
      , CAST(CASE WHEN (n + 1) % 5 = 0 THEN 'Buzz' ELSE NULL END AS varchar(max))
      , CAST(CASE WHEN (n + 1) % 15 = 0 THEN 'FizzBuzz' ELSE NULL END AS varchar(max))
      , max_n
    FROM
        FizzBuzz
    WHERE
        n < max_n
  )
SELECT
    COALESCE(fizzbuzz, fizz, buzz, CAST(n AS varchar(max)))
FROM
    FizzBuzz
ORDER BY
    n

ひとつひとつの条件を簡略化するために、COALESCE を使っているところがポイントです。多分。

・・・っと、文字列連結という手がありましたか。
と言うことで、上のエントリを参考に簡略化。

WITH
  Input(n) AS (SELECT 100)
, FizzBuzz(n, fizz, buzz, max_n) AS (
    SELECT
        1
      , CAST(NULL AS varchar(max))
      , CAST(NULL AS varchar(max))
      , n
    FROM
        Input
    UNION ALL
    SELECT
        n + 1
      , CAST(CASE WHEN (n + 1) % 3 = 0 THEN 'Fizz' ELSE NULL END AS varchar(max))
      , CAST(CASE WHEN (n + 1) % 5 = 0 THEN 'Buzz' ELSE NULL END AS varchar(max))
      , max_n
    FROM
        FizzBuzz
    WHERE
        n < max_n
  )
SELECT
    COALESCE(fizz + buzz, fizz, buzz, CAST(n AS varchar(max)))
FROM
    FizzBuzz
ORDER BY
    n