How to get all combinations of records from a table
-
Please Everyone help me. I have a situation here in which I am asked to get all the combinations of records from a table.For example let table TEST has 4 records.
A B C D
The various combinations are
A,B,C,D,AB,AC,AD,BC,BD,CD,ABC,ABD,ACD,BCD,ABCD.
How to get all these ,if total number of records are N then there would be (2^N )-1 records.
Please guide me how to do it.
many Many thanks in advance.
-
You can review CROSS JOIN
CREATE TABLE #test
(Field1 CHAR(1),
Field2 VARCHAR(50)) ON [PRIMARY]
INSERT INTO #test VALUES ('A', 'A1')
INSERT INTO #test VALUES ('B', 'B2')
INSERT INTO #test VALUES ('C', 'C3')
INSERT INTO #test VALUES ('D', 'D4')
SELECT *
FROM #test T1
CROSS JOIN #test T2
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Getting all possible combinations in a query is usually a mistake.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
It can be one procedure with the combination of some cursor or joins..
I am not getting any clue as how to procedd in this direction.
Please help me out.
-
Wouldn't this be easier to just do select * from yourtable and table on te client side write the code to loop in the recordset and load the data in an array and then display it?
I'm not too sure why you need this, but I can't see a simple way to do what you want to accomplish because you basically add a loop each time you add a record (A = 1 Loop, AB = 2 Loops, ABC = 3 Loops, ABCD = 4 Loops, ABCDE = 5 Loops).
-
As the others have said, this is best done (if you really need to do this) by the client application. That said, you could do something like following. Be advised that due to the string concatenation, this only works as long the total length of the field you are enumerating, concatenated across all rows, is not greater than 8000 characters. The example source table contains 10 rows, and thus 1023 combinations, using the equation:
C(n,r) = n! / ( r!(n-r)! )
You need to sum this equation for n=Count(*), r = 1 to Count(*) to get the number of rows expected.
------------------------------------------------
-- CODE
------------------------------------------------
-- source table
CREATE TABLE #enum
(
field char(1)
)
-- temporary output table
CREATE TABLE #results (result varchar(8000))
SET NOCOUNT ON
INSERT INTO #enum VALUES ('A')
INSERT INTO #enum VALUES ('B')
INSERT INTO #enum VALUES ('C')
INSERT INTO #enum VALUES ('D')
INSERT INTO #enum VALUES ('E')
INSERT INTO #enum VALUES ('F')
INSERT INTO #enum VALUES ('G')
INSERT INTO #enum VALUES ('H')
INSERT INTO #enum VALUES ('I')
INSERT INTO #enum VALUES ('J')
SET NOCOUNT OFF
DECLARE @i int, @i_max int, @strI varchar(8000)
DECLARE @select varchar(8000), @from varchar(8000), @where varchar(8000)
SELECT @i_max = COUNT(*) FROM #enum
/*
IF @i_max > 8000 -- only valid if the length of #enum.field = 1
BEGIN
RAISERROR ('Number of rows cannot exceed 8000', 1, 1)
RETURN
END
*/
---- Initial setup for single value list, which is just the whole source table
SET @select = 'SELECT t1.field '
SET @from = ' FROM #enum t1 '
SET @where = ' WHERE t1.field = t1.field '
SET NOCOUNT ON
INSERT #results EXEC(@select + ' AS result ' + @from + @where)
SET @i = 1
WHILE @i < @i_max
BEGIN
SET @i = @i + 1
SET @strI = CONVERT(varchar(6), @i)
SET @select = @select + ' + t' + @strI + '.field'
SET @from = @from + ' CROSS JOIN #enum t' + @strI
SET @where = @where + ' AND t' + CONVERT(varchar(6), @i - 1) + '.field < t' + @strI + '.field'
INSERT #results EXEC(@select + ' AS result ' + @from + @where)
END
SET NOCOUNT OFF
SELECT * FROM #results
DROP TABLE #enum
DROP TABLE #results
-
thank you Remi and keast
it worked!!!
Thanks
-
Well, it has been mentioned before, that you might be better off doing this at the client. If you base your solution on Joe's suggestion along with a SELECT like this
SELECT REPLACE(COALESCE(c1,'')+COALESCE(c2,'')+COALESCE(c3,'')+COALESCE(c4,''),' ','') a
FROM Combinations
GROUP BY REPLACE(COALESCE(c1,'')+COALESCE(c2,'')+COALESCE(c3,'')+COALESCE(c4,''),' ','')
you can also get rid of this nasty dynamic sql thingy.
You can also use DISTINCT to get rid of duplicates. That's somewhat up to you.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply