September 12, 2009 at 7:49 am
Okay guys, this is a fun problem, and for some reason I am having trouble with it.
Assume a set of six items. A,B,C,D,E,F
For each item in the set, I need to generate all the combinations of the other items. An example follows, which I hope is complete. The rules for combining are.
1. Single letter is present more than once.
2. Letters are always presented alphabetically from left to right. (CBDEF is not allowed.)
3. Combinations must be generated for each combo length from 1 to 5. Run the example and you will see what this means.
declare @sample table (test char(1),combos varchar(5))
insert into @sample
select 'A','BCDEF' union all
select 'A','BCDE' union all
select 'A','BCDF' union all
select 'A','BDEF' union all
select 'A','BCEF' union all
select 'A','CDEF' union all
select 'A','BCD' union all
select 'A','BCE' union all
select 'A','BCF' union all
select 'A','BDE' union all
select 'A','BDF' union all
select 'A','BEF' union all
select 'A','CDE' union all
select 'A','CDF' union all
select 'A','CEF' union all
select 'A','DEF' union all
select 'A','BC' union all
select 'A','BD' union all
select 'A','BE' union all
select 'A','BF' union all
select 'A','CD' union all
select 'A','CE' union all
select 'A','CF' union all
select 'A','DE' union all
select 'A','DF' union all
select 'A','EF' union all
select 'A','B' union all
select 'A','C' union all
select 'A','D' union all
select 'A','E'
select * from @sample
order by LEN(combos) ,combos
For the benefit of Jeff and others who would like to know why, I will explain what the purpose of this is. The various letters represent columns, with values that may or may not be dependent on one another. I need to take counts of all values of A based on all combinations of B-F, then all values of B for combinations of A and C-F, etc. I was going to use the result set from this to generate dynamic SQL to build the counts, but I am open to other ideas. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 12, 2009 at 8:12 am
Depending on exactly what you are trying to accomplish, you might be better off using the WITH CUBE option. It will give you summaries for every possible combination of the GROUP BY columns.
SELECT A, B, C, D, E, F, Count(ID)
FROM YourTable
GROUP BY A, B, C, D, E, F
WITH CUBE
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2009 at 8:30 am
Thanks, Drew. I just took a look at that, and results are promising. If WITH CUBE doesn't get me all the way there, it's still a big step in the right direction. I'll post back here later.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 13, 2009 at 10:07 am
If the with cube doens't turn out with your expected results, I think this will get you there.
DECLARE @INITIALVALUES TABLE
(
VALUE CHAR(1)
)
INSERT INTO @INITIALVALUES
SELECT ''
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'F'
;
WITH CTE (INITIALVALUE, COMBO) AS
(
SELECT
I1.VALUE AS INITIALVALUE
,REPLACE(I2.VALUE + I3.VALUE + I4.VALUE + I5.VALUE + I6.VALUE,' ','') AS COMBO
FROM
@INITIALVALUES I1
CROSS JOIN @INITIALVALUES I2
CROSS JOIN @INITIALVALUES I3
CROSS JOIN @INITIALVALUES I4
CROSS JOIN @INITIALVALUES I5
CROSS JOIN @INITIALVALUES I6
WHERE
(--no '' value for initialvalue
I1.VALUE != ''
)
AND
(--ELIMATES INITAL VALUE FROM RESULT SET
CHARINDEX(I1.VALUE,REPLACE(I2.VALUE + I3.VALUE + I4.VALUE + I5.VALUE + I6.VALUE,' ','')) = 0
)
AND
(--ELIMATES DUPLICATES WITHIN COMBO
CHARINDEX(I2.VALUE,REPLACE(I3.VALUE + I4.VALUE + I5.VALUE + I6.VALUE,' ','')) = 0
AND
CHARINDEX(I3.VALUE,REPLACE(I2.VALUE + I4.VALUE + I5.VALUE+I6.VALUE,' ','')) = 0
AND
CHARINDEX(I4.VALUE,REPLACE(I2.VALUE + I3.VALUE + I5.VALUE+I6.VALUE,' ','')) = 0
AND
CHARINDEX(I5.VALUE,REPLACE(I2.VALUE + I3.VALUE + I4.VALUE+I6.VALUE,' ','')) = 0
AND
CHARINDEX(I6.VALUE,REPLACE(I2.VALUE + I3.VALUE + I4.VALUE+I5.VALUE,' ','')) = 0
)
)
SELECT
INITIALVALUE
, COMBO
FROM
CTE
WHERE --SELECT COMBO'S IN ALPHA ORDER
ISNULL(ASCII(SUBSTRING(COMBO,1,1)),99) <= ISNULL(ASCII(SUBSTRING(COMBO,2,1)),99)
AND
ISNULL(ASCII(SUBSTRING(COMBO,2,1)),99) <= ISNULL(ASCII(SUBSTRING(COMBO,3,1)),99)
AND
ISNULL(ASCII(SUBSTRING(COMBO,3,1)),99) <= ISNULL(ASCII(SUBSTRING(COMBO,4,1)),99)
AND
ISNULL(ASCII(SUBSTRING(COMBO,4,1)),99) <= ISNULL(ASCII(SUBSTRING(COMBO,5,1)),99)
AND
COMBO !=''
GROUP BY
INITIALVALUE
, COMBO
ORDER BY
INITIALVALUE
, LEN(COMBO) DESC
, COMBO
September 13, 2009 at 10:27 am
Many thanks, Roger.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply