SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouped permutations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vr8ce
Starting Member

23 Posts

Posted - 06/12/2013 :  13:45:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/12/2013 :  14:41:01  Show Profile  Reply with Quote
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
Go to Top of Page

vr8ce
Starting Member

23 Posts

Posted - 06/12/2013 :  14:50:09  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/13/2013 :  04:10:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000