CODEWITHSUNDEEP
sqlsql-server
Søren Pedersen
Søren Pedersen

Reputation: 21

Select all combinations of values from one column

I need to return all combinations of values from one column in a table. The result should by sorted.

----------------------
Table A
----------------------
ID      TEXT
----------------------
100     AAA
100     BBB
100     CCC
200     DDD
200     EEE

Expected result:

100     /AAA
100     /BBB
100     /CCC
100     /AAA/BBB
100     /AAA/CCC
100     /BBB/CCC
100     /AAA/BBB/CCC
200     /DDD
200     /EEE
200     /DDD/EEE

The resulting select statement should be able to be part of another select.

I have tried this and it seems to be working, but I cannot use it as a subquery.

WITH cte ( combination, curr ) AS (
  SELECT CAST( t.COL AS VARCHAR(80) ),
         t.COL
  FROM   TABLE_A t
  UNION ALL
  SELECT CAST( c.combination + '/' + CAST( t.COL AS VARCHAR(3) ) AS VARCHAR(80) ), t.COL
  FROM   TABLE_A t
         INNER JOIN
         cte c
         ON ( c.curr < t.COL )
)
SELECT '/' + combination FROM cte

Upvotes: 2

Views: 325

Answers (2)

EzLo
EzLo

Reputation: 14209

You can use a recursive CTE for this. The problem is that you are lacking a row number to correctly join against the next values (and never against the previous one). I assume this is what you want because you include path /AAA/BBB in your results but not /BBB/AAA so record for AAA must join with BBB, but record with BBB not with AAA, hence the need of a particular sorting method which isn't present in your data.

I simulated a row number with an IDENTITY, you can use ROW_NUMBER() with whatever OVER(PARTITION BY ID ORDER BY <expression or column> ) that you want.

Set up:

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

CREATE TABLE #Values(
    RowID INT IDENTITY,
    ID INT,
    Text VARCHAR(100))

INSERT INTO #Values (
    ID,
    Text)
VALUES
    (100, 'AAA'),
    (100, 'BBB'),
    (100, 'CCC'),
    (200, 'DDD'),
    (200, 'EEE')

Solution:

;WITH RecursiveJoins AS
(
    -- Anchor (original row)
    SELECT
        OriginRowID = V.RowID,
        CurrentRowID = V.RowID,
        ID = V.ID,
        Path = CONVERT(VARCHAR(MAX), '/' + V.Text),
        RecursionLevel = 0
    FROM
        #Values AS V

    UNION ALL

    -- Recursion (add any value with the same ID and higher RowID)
    SELECT
        OriginRowID = R.OriginRowID,
        CurrentRowID = V.RowID,
        ID = R.ID,
        Path = R.Path + '/' + V.Text,
        RecursionLevel = R.RecursionLevel + 1
    FROM
        RecursiveJoins AS R
        INNER JOIN #Values AS V ON
            R.ID = V.ID AND
            R.CurrentRowID < V.RowID
)
SELECT
    R.ID,
    R.Path,
    R.RecursionLevel
FROM
    RecursiveJoins AS R
ORDER BY
    R.ID,
    R.RecursionLevel,
    R.Path

Result:

ID      Path            RecursionLevel
100     /AAA            0
100     /BBB            0
100     /CCC            0
100     /AAA/BBB        1
100     /AAA/CCC        1
100     /BBB/CCC        1
100     /AAA/BBB/CCC    2
200     /DDD            0
200     /EEE            0
200     /DDD/EEE        1

Upvotes: 1

UsmanMirza
UsmanMirza

Reputation: 276

Here you go, below query will give you expected output:

Here Creating temp table to perform the operation:

  select * into #tt from (
  select '100' as ID,'AAA' as TEXT
  union all 
  select '100' as ID,'BBB' as TEXT
 union all 
  select '100' as ID,'CCC' as TEXT
 union all 
 select '200' as ID,'DDD' as TEXT
 union all 
 select '200' as ID,'EEE' as TEXT
)a

select * from #tt 
GO

Here your actual problem solution starts:

WITH cte ( ID,Val, curr ) AS (
  SELECT t.ID,CAST( t.[TEXT] AS VARCHAR(max) ),
        t.[TEXT]
  FROM   #tt t
  where t.id = 100
 UNION ALL
 SELECT t.ID, CAST( c.Val + '/' + CAST( t.[TEXT] AS VARCHAR(max) ) AS VARCHAR(max) ),
      t.[TEXT]
 FROM   #tt t
     INNER JOIN
     cte c
     ON ( c.curr < t.[TEXT] )
     where t.id = 100
)
,cte2 ( ID,Val, curr ) AS (
 SELECT t.ID,CAST( t.[TEXT] AS VARCHAR(max) ),
       t.[TEXT]
 FROM   #tt t
 where t.id = 200
  UNION ALL
  SELECT t.ID, CAST( c.Val + '/' + CAST( t.[TEXT] AS VARCHAR(max) ) AS VARCHAR(max) 
),
     t.[TEXT]
 FROM   #tt t
      INNER JOIN
      cte2 c
      ON ( c.curr < t.[TEXT] )
     where t.id = 200
)

SELECT ID,concat('/',Val)
FROM cte 
union all 
SELECT ID,concat('/',Val)
FROM cte2 
order by 1

Upvotes: 0

Related Questions