Generating combinations (cells of a multidimensional cube?)

Hi,
(Apologies if I've posted this to the wrong group.)
I have a table of Categories and Category Items, and need to generate
every possible combination, e.g. I have:
CategoryID CategoryItemID
-- --
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
3 3
and I want to generate:
CellID CategoryID CategoryItemID
-- -- --
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 2
...
24 1 2
24 2 4
24 3 3
I *could* write a stored proc that iterates over each item in turn to
build up the results set, but it's clunky and inelegant - is there a
smarter way to do this?
Thanks in advance,
NateHi Nate,
You can use following logic to generate every possible combination but
couldnt understand your example.
See if this help.
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select distinct CategoryID from #temptab) a
cross join (select distinct CategoryItemID from #temptab) b
Regards,
Lakshman.|||Lakshman, thanks for replying.
Sorry, I've not been clear enough. Say I have as input:
Category CategoryItem
-- --
Fruit Apple
Fruit Orange
Car Ford
Car Vauxhall
Car Rover
Car Fiat
Animal Cat
Animal Dog
Animal Tortoise
I need to generate all the possible combinations of the 3 categories:
Comb. # Category CategoryItem
-- -- --
1 Fruit Apple
1 Car Ford
1 Animal Cat
2 Fruit Apple
2 Car Ford
2 Animal Dog
...
24 Fruit Orange
24 Car Fiat
24 Animal Tortoise
Is that any clearer?
(Of course, I need to be able to extend this to a non-fixed number of
categories)|||If this doesn't do what you want, then please see my signature so you can
understand why good specs are better than word problems.
You might also consider storing these in separate tables instead of trying
to partially relate them, if there really is no relevance to the
relationship...
USE TempDB
GO
CREATE TABLE dbo.Categories
(
CategoryID INT,
CategoryItemID INT
)
GO
SET NOCOUNT ON
INSERT dbo.Categories SELECT 1, 1
INSERT dbo.Categories SELECT 1, 2
INSERT dbo.Categories SELECT 2, 1
INSERT dbo.Categories SELECT 2, 2
INSERT dbo.Categories SELECT 2, 4
INSERT dbo.Categories SELECT 3, 1
INSERT dbo.Categories SELECT 3, 2
INSERT dbo.Categories SELECT 3, 3
GO
SELECT c1.CategoryID, c2.CategoryItemID
FROM dbo.Categories c1
FULL OUTER JOIN
dbo.Categories c2
ON c1.CategoryID > 0
GROUP BY c1.CategoryID, c2.CategoryItemID
ORDER BY 1, 2
GO
DROP TABLE dbo.Categories
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Nate" <spamthecan@.gmail.com> wrote in message
news:1115389621.362575.318720@.o13g2000cwo.googlegroups.com...
> Lakshman, thanks for replying.
> Sorry, I've not been clear enough. Say I have as input:
> Category CategoryItem
> -- --
> Fruit Apple
> Fruit Orange
> Car Ford
> Car Vauxhall
> Car Rover
> Car Fiat
> Animal Cat
> Animal Dog
> Animal Tortoise
> I need to generate all the possible combinations of the 3 categories:
> Comb. # Category CategoryItem
> -- -- --
> 1 Fruit Apple
> 1 Car Ford
> 1 Animal Cat
> 2 Fruit Apple
> 2 Car Ford
> 2 Animal Dog
> ...
> 24 Fruit Orange
> 24 Car Fiat
> 24 Animal Tortoise|||Hi Nate,
This example is better than earlier one.
I was able to generate all combinations (72 rows) but i should see
whehter it is possiable to add a comb#.
Let you know if i could find.
Script:
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(2, 3)
insert into #temptab values(2, 4)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select CategoryID from #temptab) a
cross join (select CategoryItemID from #temptab) b
where
exists (
select
*
from
#temptab c
where
c.CategoryID = a.CategoryID
and c.CategoryItemID = b.CategoryItemID)
order by
a.CategoryID
Regards,
Lakshman.|||Hi AB,
Thanks for replying.

>If this doesn't do what you want, then please see my signature so you
can
>understand why good specs are better than word problems.
The table in my original post is the actual table - the IDs are
integers.

>You might also consider storing these in separate tables instead of
trying
>to partially relate them, if there really is no relevance to the
>relationship...
Not entirely sure what you mean here - the categories are unrelated to
each other, but they are all categories. Each row in the CategoryItem
table has a parent Category.
When I run your code, I get impossible combinations, e.g. (1,3) and
(1,4), but there are only two types of Fruit (to use the example in
words from my 2nd post).
I appreciate that I probably haven't explained the situation very well!|||Ah, Lakshman, that's great - I feel you're on to something there!
Using the EXISTS clause like that is something I didn't consider.|||> I appreciate that I probably haven't explained the situation very well!
That's why I have already suggested you read http://www.aspfaq.com/5006 !
It won't hurt you to learn how to "explain the situation" in terms we can
understand and, more importantly, reproduce.|||Nate,
If you have a fixed and known number of categories, it's not as
tricky. See if this helps:
create table Categories (
pk int primary key,
Category varchar(10)
)
insert into Categories values (1,'Fruit')
insert into Categories values (2,'Car')
insert into Categories values (3,'Animal')
go
create table Items (
pk int primary key,
Categorypk int,
CategoryItem varchar(10)
)
insert into Items values (1,1,'Apple')
insert into Items values (2,1,'Orange')
insert into Items values (3,2,'Ford')
insert into Items values (4,2,'Vauxhall')
insert into Items values (5,2,'Fiat')
insert into Items values (6,3,'Dog')
insert into Items values (7,3,'Cat')
go
create function f(
@.pk int
) returns table as return
select C.Category, CI.CategoryItem
from Categories as C join Items as CI
on C.pk = @.pk
and CI.Categorypk = @.pk
and C.pk = CI.Categorypk
go
select
identity(int,1,1) as Combopk,
A.CategoryItem as AI,
B.CategoryItem as BI,
C.CategoryItem as CI
into #temp
from f(1) as A, f(2) as B, f(3) as C
select
T.Combopk,
C.Category,
case C.pk when 1 then T.AI when 2 then T.BI when 3 then T.CI end as Item
from #temp T
cross join Categories C
order by 1,2
go
--drop table Categories, Items, #temp
--drop function f
--Steve Kass
--Drew University
Nate wrote:

>Hi AB,
>Thanks for replying.
>
>
>can
>
>The table in my original post is the actual table - the IDs are
>integers.
>
>
>trying
>
>Not entirely sure what you mean here - the categories are unrelated to
>each other, but they are all categories. Each row in the CategoryItem
>table has a parent Category.
>When I run your code, I get impossible combinations, e.g. (1,3) and
>(1,4), but there are only two types of Fruit (to use the example in
>words from my 2nd post).
>I appreciate that I probably haven't explained the situation very well!
>
>|||Hi Steve,
Many thanks for that. In some cases, the number of categories will be
fixed (or at least, will have a sensible upper bound), so I can extend
your code to cover those cases. Cheers!
Regards,
Nate