Grouped permutations - 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)

Grouped permutations

Author

Topic

vr8ce
Starting Member

23 Posts

Posted - 2013-06-12 : 13:45:56

I've looked at a few permutation posts here, but I haven't been able to translate them to this particular problem.

I have a number of questions q, each of which can have several answers a. Here is some sample data.

declare @an table (
  q tinyint,
  a tinyint
)

insert into @an values (1,1),
                       (1,2),
                       (2,1),
                       (3,1),
                       (3,2),
                       (3,3),
                       (4,1),
                       (5,1),
                       (5,2)

I'm trying to create a query that will bring back all the permutations of answers for a subset of the questions. Short of that, a procedure that will do the same.

For example, if the subset of questions is 1 and 3, then I need all the permutations of how questions 1 and 3 can be answered together. The first question (question 1) can be answered two ways, and the second question (question 3) can be answered three ways. Thus, the result set would be (the first column just groups the sets of answers, and the blank lines are just for clarity):

s1,1,1
s1,3,1

s2,1,1
s2,3,2

s3,1,1
s3,3,3

s4,1,2
s4,3,1

s5,1,2
s5,3,2

s6,1,2
s6,3,3

The subset of questions is dynamic and can be from 1 question up to the entire list of questions. In other words, I chose a subset of two questions, but it could be two or three or five or whatever.

Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-12 : 14:41:01

As a proof of concept, you should be able to do a cross join like shown below.

SELECT * FROM 
(SELECT q AS q1,a AS a1 FROM @an WHERE q = 1)A
CROSS JOIN 
(SELECT q AS q3,a AS a3 FROM @an WHERE q =3)B

vr8ce
Starting Member

23 Posts

Posted - 2013-06-12 : 14:50:09

I've played with something like that, but it has a few big problems.

First, the list of questions in the subset isn't known. That means the sql would have to be built as dynamic sql, and just the code to do that would be semi-messy.

Second, the result set would have a variable list of columns (depending on the size of the subset of questions), and thus would be difficult to parse.

Third, each set of answers is a single row, and it needs to be a set of rows.

I feel certain this can be done with one or more CTE's, I just can't visualize how.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 04:10:46

it would require a dynamic sql like below
Also tables all have to be permananent tables if you want them to be used inside dynamic sql


DECLARE @List varchar(100),@Qn int

SET @List= '1,2,3'

SELECT @Qn = MIN(q)
FROM an
WHERE ',' + @List + ',' LIKE '%,' + q + ',%'

WHILE @Qn IS NOT NULL
BEGIN
EXEC('SELECT * INTO Q' + @Qn + ' FROM an WHERE q = ' + @Qn)

SELECT @Qn = MIN(q)
FROM an
WHERE ',' + @List + ',' LIKE '%,' + q + ',%'
AND q > @Qn
END

SELECT @List = STUFF((SELECT DISTINCT ',' + q FROM an WHERE ',' + @List + ',' LIKE '%,' + q + ',%' ORDER BY ',' + q FOR XML PATH('')),1,1,'')

EXEC ('SELECT * FROM Q' + REPLACE(@List,',',' CROSS JOIN Q'))

--clean up if required
EXEC ('DROP TABLE Q' + REPLACE(@List,',',';DROP TABLE Q'))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources