Set operation on TSQL (SQL 2005/2008)

When a set is gi开发者_运维百科ven say {1,2,3,4,5,6} The task is to separe pair of subsets

{1,2},
{1,3},
{1,4},
{1,5},
{1,6},
{2,3},
{2,4},
{2,5},
{2,6},
{3,4},
{3,5},
{3,6},
{4,5},
{5,6}

So when i have a table

Table Element
1
2
3
4
5
6

What is the way to list out all possible pair of comma separated subset ? (Duplicates can be ignored (i.e) {1,2} is identical to {2,1})

SELECT T1.elem, T2.elem
FROM MyTable T1
INNER JOIN MyTable T2
ON T2.elem > T1.elem

...gets you most of the way there - if you want these shown as sets then...

SELECT '{' + CAST(T1.elem AS VARCHAR(12)) + ', ' + CAST(T2.elem AS VARCHAR(12)) + '}'
FROM MyTable T1
INNER JOIN MyTable T2
ON T2.elem > T1.elem

...is what you're after.

Here is a solution to the problem using a CTE. It isn’t particularly elegant, but it gets the job done.

DECLARE @set TABLE (Element INT);

INSERT INTO @set(Element) VALUES (1);
INSERT INTO @set(Element) VALUES (2);
INSERT INTO @set(Element) VALUES (3);
INSERT INTO @set(Element) VALUES (4);
INSERT INTO @set(Element) VALUES (5);
INSERT INTO @set(Element) VALUES (6);

;WITH array (Element1, Element2, Row)
AS
(
SELECT t.Element
     , t2.Element
     , ROW_NUMBER() OVER(ORDER BY t.Element)
  FROM @set AS t
 CROSS JOIN @set AS t2
 WHERE t.Element <> t2.Element  
)
SELECT a.Element1
     , a.Element2
     , '{' + CONVERT(VARCHAR(5),a.Element1) + ',' + CONVERT(VARCHAR(5),a.Element2) + '}' AS 'Subset'
  FROM array AS a
 WHERE NOT EXISTS (SELECT *
                     FROM array AS sa
                    WHERE sa.Element1 = a.Element2 
                      AND sa.Element2 = a.Element1 
                      AND sa.Row < a.Row 
                  );