• Frédéric BROUARD (1/8/2009) ...

    Here's something similar for comparison, making use of powers of 2 rather than LIKE, and XML rather than string manuipulation.

    --preparation

    IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1

    GO

    --/

    --structure

    CREATE TABLE #t1 (x VARCHAR(MAX))

    --/

    --data

    INSERT INTO #t1 VALUES ('ABC')

    INSERT INTO #t1 VALUES ('DEF')

    INSERT INTO #t1 VALUES ('GHI')

    --/

    --parameters

    DECLARE @AllowDuplicates BIT

    SET @AllowDuplicates = 0

    --/

    --query

    ; WITH

    a AS (SELECT COUNT(*) AS cnt FROM #t1)

    , b AS (SELECT POWER(2, ROW_NUMBER() OVER(ORDER BY x)-1) AS marker, x FROM #t1)

    , c AS (SELECT marker, 1 as level, '<x>' + x + '</x>' AS x FROM b UNION ALL

    SELECT c.marker + b.marker, c.level + 1, c.x + '<x>' + b.x + '</x>'

    FROM b INNER JOIN c ON (@AllowDuplicates = 1 OR b.marker & c.marker = 0)

    WHERE c.level < (SELECT cnt FROM a))

    , d AS (SELECT ROW_NUMBER() OVER(ORDER BY x) as permutation, cast(x as xml) as xml

    FROM c WHERE level = (SELECT cnt FROM a))

    SELECT

    d.permutation,

    ROW_NUMBER() OVER(PARTITION BY d.permutation ORDER BY d.permutation) AS position,

    c.value('.', 'varchar(100)') AS value

    FROM d CROSS APPLY xml.nodes('//x') T(c)

    --/

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.