Dynamically decide number of joins

Question

I have two tables. Table 1: Question_Master which contains the questions

 id   question
    1    Q1
    2    Q2
    3    Q3

Table 2: Option Master Which contains the Options

id   option
1    H
2    N
3    S

I want all the combinations of options for all the questions.

Something Like this

 Q1 Q2  Q3
    H   H   H
    H   H   N
    H   H   s
    H   N   H

NOTE: There can be any number of records in both table.If it has 4 records in option_master than i want all combination for 4 records.

Hi have tried but the problem is ,how to decide number of cross join dynamically.Based on the number of records in question_master — gkarya42, Dec 03 '15 at 04:30
Look for `Dynamic pivoting table sql server` you will find lots of answers! — Jorge Campos, Dec 03 '15 at 04:32
http://stackoverflow.com/questions/12430512/dynamic-pivot-table-in-sql-server — Jorge Campos, Dec 03 '15 at 04:32

score 1 · Answer 1 · answered Dec 03 '15 at 07:14

You need to CROSS JOIN the Option_Master with itself. And then you need to cross join the result again with Option_Master. This has to be repeated for each question. I think this has to be done by dynamically creating the SQL statement. Try this example to get an idea:

declare @NumberOfQuestions int

set @NumberOfQuestions = (
    select count(*) 
    from question_master
    )

declare @sql varchar(max)

set @sql = 'select om1.opt '

declare @counter int

set @counter = 2
while @Counter <=  @NumberOfQuestions
begin
    set @sql = @sql + '
    , om' + cast (@counter as varchar(1)) + '.opt '
    set @counter = @counter + 1
end

set @sql = @sql + '
    from option_master om1 '

set @counter = 2
while @Counter <=  @NumberOfQuestions
begin
    set @sql = @sql + '
    cross join option_master om' + cast(@counter as varchar(1)) + ' '
    set @counter = @counter + 1
end

set @sql = @sql + '
    order by om1.opt '

set @counter = 2
while @Counter <=  @NumberOfQuestions
begin
    set @sql = @sql + '
    , om' + cast(@counter as varchar(1)) + '.opt '
    set @counter = @counter + 1
end

exec (@sql)

Albert

Change exec(@Sql) into print(@sql) to see the generated SQL – Albert Hugo Dec 03 '15 at 07:18 — Albert Hugo, Dec 03 '15 at 07:18

score 1 · Accepted Answer · answered Dec 03 '15 at 21:10

You can do it dynamically by using some string concatenation queries to build out the Select statement based on the Question_Master table values

DECLARE @SelectSQL VARCHAR(MAX),
        @JoinSQL VARCHAR(MAX),
        @OrderSQL VARCHAR(MAX)

SELECT  @SelectSQL = COALESCE(@SelectSQL + ',', '') 
             + QUOTENAME(question) + '.[option] as ' + QUOTENAME(question),
        @JoinSQL = COALESCE(@JoinSQL + ' CROSS JOIN ', '') 
             + 'Option_Master as ' + QUOTENAME(question),
        @OrderSQL = COALESCE(@OrderSql + ',', '') 
             + QUOTENAME(question) + '.[option]'
FROM Question_Master
ORDER BY question


DECLARE @Sql AS NVARCHAR(MAX) = N'SELECT ' + @SelectSQL + ' FROM ' + @JoinSQL + ' ORDER BY ' + @OrderSQL
EXECUTE sp_executesql @Sql;

using QUOTENAME will allow you to have questions that have spaces or some other characters in the value.

SQL Fiddle Example

you are a genius.sometimes we make things complicated.I was trying to solve using pivot,Thanks — gkarya42, Dec 04 '15 at 06:36

Dynamically decide number of joins

2 Answers2