-- Prepare sample data
DECLARE	@Sample TABLE
	(
		col1 VARCHAR(20) NOT NULL,
		col2 VARCHAR(20) NOT NULL
	);

INSERT	@Sample
	(
		col1,
		col2
	)
VALUES	('Value1', 'Value2'),
	('Value1', 'Value3'),
	('Value4', 'Value2'),
	('Value3', 'Value1');

-- Solution by SwePeso
CREATE TABLE	#Cols
		(
			col1 VARCHAR(20) NOT NULL,
			col2 VARCHAR(20) NOT NULL,
			cnt INT NOT NULL
		);

INSERT		#Cols
		(
			col1,
			col2,
			cnt
		)
SELECT		x.col1,
		y.col2,
		0 AS cnt
FROM		(
			SELECT	col1 AS col1
			FROM	@Sample

			UNION

			SELECT	col2
			FROM	@Sample
		) AS x
INNER JOIN	(
			SELECT	col1 AS col2
			FROM	@Sample

			UNION

			SELECT	col2
			FROM	@Sample
		) AS y ON y.col2 > x.col1
ORDER BY	x.col1,
		y.col2;

WITH cteSource(col1, col2)
AS (
	SELECT	CASE
			WHEN col1 <= col2 THEN col1
			ELSE col2
		END AS col1,
		CASE
			WHEN col1 <= col2 THEN col2
			ELSE col1
		END AS col2
	FROM	@Sample
)
UPDATE		c
SET		c.cnt = w.Items
FROM		#Cols AS c
INNER JOIN	(
			SELECT		col1,
					col2,
					COUNT(*) AS Items
			FROM		cteSource
			GROUP BY	col1,
					col2
		) AS w ON w.col1 = c.col1
			AND w.col2 = c.col2;

SELECT		col1,
		col2,
		cnt
FROM		#Cols
ORDER BY	col1,
		col2;

DROP TABLE	#Cols;

CREATE TABLE #A (ID INT NOT NULL IDENTITY(1,1), x INT, y INT)
INSERT INTO #A VALUES (1,2),(1,3),(4,2),(1,3);

;WITH Cartesian AS
(
	SELECT DISTINCT 
		a.x,
		b.y 
	FROM 
		#A a CROSS JOIN #A b
),
Combinations AS
(
	SELECT
		a.ID,
		a.x,
		b.y
	FROM
		#A a INNER JOIN #A b ON
			a.ID = B.Id
)
SELECT
	a.*,
	b.N
FROM
	Cartesian a
	OUTER APPLY
	(
		SELECT COUNT(*) AS N FROM
		Combinations c
		WHERE c.x = a.x AND c.y = a.y
	)b;

DROP TABLE #A

quote:

---

Originally posted by SwePeso

For a combination; isn't {Value1, Value3} then same as {Value3, Value1} ?

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by SwePeso

For a combination; isn't {Value1, Value3} then same as {Value3, Value1} ?

---

N 56°04'39.26"
E 12°55'05.63"

---

Yes. May be OP actually meant permutations? But not sure because the sample data was such that combinations and permutations yielded the same set.

---