Displaying every combo of unique id values
-
Displaying every combo of unique id values
I have a table with an ID and desc
ID Desc
1 Bill
2 Sam
3 John
4 Mike
I need to create a table (automatically) that will
create a 'combo' of each potential grouping of ID(s)
i.e. You can have:
(1) Bill alone,
Sam alone, etc.
or
(2) Bill and Sam,
Bill and John
Bill and Mike
Sam and John, etc.
or
(3) Bill, Sam and John
Bill, Mike and John ,etc.
or
(4) Bill, Sam, John and Mike
This is what the newly created table should look like. GroupID is created for each unique combo of IDs
GroupID ID
1 1
2 2
3 3
4 4
5 1
5 2
6 1
6 3
7 1
7 4
8 1
8 2
8 3
9 1
9 2
9 3
9 4
Hope this isn't too confusing. Thanks for any assistance!!!
-
Have a look at CROSS JOIN in BOL. If I understand you correct that should give you what you need.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank,
Unfortunatelky that will not give the response I'm looking for.
Will return
Itm_ItemID Expr1
1 1
2 1
3 1
4 1
1 2
2 2
3 2
4 2
1 3
2 3
3 3
4 3
1 4
2 4
3 4
4 4
Thanks,
Dan
-
Apparently I've misunderstood you. Will dig into this...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I will look this weekend, but I have a dynamic SQL script that includes an ALTER TABLE and loops through a table, (or two) to generate something like what I think you want. I will see if I can find it, but you may think it up yourself.
I wasn't born stupid - I had to study.
-
This is hardcoded for up to 4 ID's ( but it would not be too hard to generate the dynamics )
Good Luck.
/************************************************************************************/
-- setup
set nocount on
create table #t([ID] int identity, [Desc] varchar(10))
insert #t select 'Bill' union select 'Sam' union select 'John' union select 'Mike'
create table #permutations(GroupID int identity, p char(256))
insert #permutations -- 1 value
select cast(t1.[ID] as char(8))
from #t t1
insert #permutations -- 2 values
select cast(t1.[ID] as char(8))+cast(t2.[ID] as char(8))
from #t t1 join #t t2 on t1.[ID] < t2.[ID]
insert #permutations -- 3 values
select cast(t1.[ID] as char(8))+cast(t2.[ID] as char(8))+cast(t3.[ID] as char(8))
from #t t1 join #t t2 on t1.[ID] < t2.[ID] join #t t3 on t2.[ID] < t3.[ID]
insert #permutations -- 4 values
select cast(t1.[ID] as char(8))+cast(t2.[ID] as char(8))+cast(t3.[ID] as char(8))+cast(t4.[ID] as char(8))
from #t t1 join #t t2 on t1.[ID] < t2.[ID] join #t t3 on t2.[ID] < t3.[ID] join #t t4 on t3.[ID] < t4.[ID]
-- Here we create the long table with all the groups...
select GroupID,substring(p,1+8*tally.n,8) as [ID] from #permutations
join (select 0 as n union select 1 union select 2 union select 3 ) tally -- 4 values tally
on len(#permutations.p) > 8*tally.n
order by 1,2
-- cleanup
drop table #permutations
drop table #t
/************************************************************************************/
You must unlearn what You have learnt -
How about something like:
create table #TempData (ID int)
insert into #TempData values ( 1 )
insert into #TempData values ( 2 )
insert into #TempData values ( 3 )
insert into #TempData values ( 4 )
Create Table #TempResults (GroupID int identity, ID1 int, ID2 int, ID3 int, ID4 int)
Insert into #TempResults (ID1, ID2, ID3, ID4)
Select temp1.ID as ID1 , temp2.ID as ID2, 0 as ID3,0 as ID4 from #TempData Temp1, #TempData Temp2
UNION
Select temp1.ID as ID1 , temp2.ID as ID2, temp3.ID as ID3,0 as ID4 from #TempData Temp1, #TempData temp2, #TempData temp3
UNION
Select temp1.ID as ID1 , temp2.ID as ID2, temp3.ID as ID3,temp4.ID as ID4 from #TempData Temp1, #TempData temp2, #TempData temp3, #TempData temp4
select * from #TempResults
drop table #TempData
drop table #tempResults
-
How about this
CREATE TABLE #TableB (GroupID int, [ID] int)
DECLARE @groupid int, @id int, @max-2 int, @CT int
SELECT @max-2 = MAX([ID]) FROM [TableA]
SET @groupid = 0
SET @id = 1
WHILE @id <= @max-2
BEGIN
SET @groupid = @groupid + 1
INSERT INTO [TableB] (GroupID, [ID]) VALUES (@groupid, @id)
SET @CT = @id + 1
BEGIN
SET @groupid = @groupid + 1
INSERT INTO [TableB] (GroupID, [ID]) VALUES (@groupid, @id)
INSERT INTO [TableB] (GroupID, [ID]) VALUES (@groupid, @CT)
IF (@ct - @id) > 1
BEGIN
SET @groupid = @groupid + 1
INSERT INTO [TableB] (GroupID, [ID])
SELECT @groupid, [ID] FROM [TableA]
WHERE [ID] >= @id AND [ID] <= @CT
END
END
SET @id = @id + 1
END
SELECT * FROM #TableB
DROP TABLE #TableB
Far away is close at hand in the images of elsewhere.
Anon. -
-- This is our data:
declare @Vals table (ID int)
insert into @vals
select 1 union
select 2 union
select 3 union
select 4 union
select 5
-- we need numbers, from 1 through 2 ^ (# of values)
-- This is just a helper table.
-- If a numbers table already exists, we don't need to
-- create this:
declare @Numbers table (N int)
declare @i int;
declare @Count int;
set @i = 0
set @count = (select count(*) from @vals)
set nocount on
while (@i < power(2, @count))
begin
set @i=@i+1
insert into @Numbers values (@i)
end
set nocount off
-- *************************
-- Here is the final answer -- a single SELECT.
-- It handles up to 32 values in the data:
select N.N as GroupID, V.ID
from @Numbers N
inner join @Vals V
on (power(2,V.ID-1) & N.N) = power(2,V.ID-1)
order by N.N, V.ID
-
warning (for my method and others):
if large amounts of data you have, results grow even larger.
For N names, possible groups = 2^N - 1.
This means only 32 names = 4,294,967,295
possible groups.
lots of rows to return, hm?
-
When you compare what is wanted, and what your nice solution produces, are you sure this is correct?
With yours I get:
1 1
2 2
3 1
3 2
4 3
5 1
5 3
6 2
6 3
While this is wanted
1 1
2 2
3 3
4 4
5 1
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It depends if GroupID has any meaning. if it is just to group each distinct set, then no difference.
if there must be some logic as to how groupID is calculated, that must be specified. (it hasn't been)
-
"select N.N as GroupID, V.ID
from @Numbers N
inner join @Vals V
on (power(2,V.ID-1) & N.N) = power(2,V.ID-1)
order by N.N, V.ID"
Very impressive Yoda!
Is this a "known" algorithm, or did you work it out yourself ?
Would You care to elaborate, I barely understand even after dissecting it.
I checked this, but did not make me much wiser.
http://mathworld.wolfram.com/Permutation.html
You must unlearn what You have learnt -
It is simply binary !
for 4 names:
0000
0001
0010
0011
0100
..etc ...
1111
1 = include the name, 0 = exclude.
For each group:
GroupID = integer representation of the bits
Members = all ID's in which the bit in their position is set to 1
-
Do the CROSS JOIN as first reply but then simply filter one ID <= the other ID
This does permutations:
select a.id, b.id
FROM sysobjects a
JOIN sysobjects b ON 1=1
WHERE a.id < 20 --limit the results to managable
AND b.id < 20 --limit the results to managable
AND a.id <= b.id
ORDER BY 1, 2
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply