Home Forums Programming General Cte for String in random order RE: Cte for String in random order

  • You may get some ideas from this:

    ;WITH

    E1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

    iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E1 a, E1 b)

    SELECT *

    FROM iTally i

    CROSS APPLY (

    SELECT Word AS 'data()'

    FROM (

    SELECT TOP (ABS(n+CHECKSUM(NEWID()))%9+1) word

    FROM (

    SELECT word = CAST('the' AS VARCHAR(200)) UNION ALL

    SELECT 'quick' UNION ALL

    SELECT 'brown' UNION ALL

    SELECT 'fox' UNION ALL

    SELECT 'jumped' UNION ALL

    SELECT 'over' UNION ALL

    SELECT 'the' UNION ALL

    SELECT 'lazy' UNION ALL

    SELECT 'dog'

    ) Words ORDER BY NEWID()) u2

    FOR XML PATH('')

    ) iTVF(Sentence)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden