Cartesian product with count? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cartesian product with count?

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-07-25 : 06:24:12

I want a Cartesian product of X amount of fields (let's say, about 12 or so fields) and then breakdown them into counts. The fields can be at one table

So for example, I have this kind of simple table, named, say, testtable
Field1 Field2
Value1 Value2
Value1 Value3
Value4 Value2
Value1 Value3

The Cartesian product is (this table is not necessarily needed and at least the Combination can be formatted in any way, doesn't matter at all)
ID Combination
1 Value1Value2
2 Value1Value3
3 Value4Value2
4 Value4Value3

And the result would be
Combination Count
1 1
2 2
3 1
4 0

It's important (pretty much the whole point) to have those zero rows there too.

I don't have the final data yet for this, but I think this should be pretty easy to understand that what is wanted. The result might be harder to count though.

If that's impossible (which I think it's not), I could have the full Cartesian product table counted in forehand.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-25 : 07:12:36

[code]-- 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;[/code]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 07:28:48

Another way:

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

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-07-25 : 07:39:45

Thanks to both of you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-25 : 07:54:40

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

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 08:03:34

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.

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-08-05 : 09:07:10

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.

Sorry for the late answer. 3,1 is not same than 1,3. The idea is that I have a huge set of values like

Yellow,Blue,Green,Gray,Black
Car,Plane,Boat
Night,Day,Evening,Morning
Left,Right,Up,Down

which I "code" (or "translate to IDs", whatever) to numbers (1,2,3,4,...,N)
so

Yellow-Car-Day-Up is 1-1-2-3 and Blue-Car-Evening-Left is 2-1-3-1 which would in SwePeso's idea (or example, whatever) be the same thing, but clearly it is not.

And again, the result I'd want is that there's (in this example) 5*3*4*4 = 240 rows
so that I can check against each one that how many of each combinations I have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources