I have three tables as below.
submission1_header
Submission1_id Submission2_id Name
100 500 Rahul
200 500 Deva
submission1_details
Submission1_id Submission2_id Question_no Answer
100 500 1 YES
100 500 2 YES
100 500 3 NO
100 500 4 NO
100 500 5 YES
200 501 1 YES
200 501 3 YES
200 501 4 YES
submission2_details
Submission2_id Question_no Answer
500 1 YES
500 2 YES
501 1 YES
501 2 YES
501 3 NA
Using below query I got the output as below (which is joining first two tables only)
==================DYNAMIC PIVOT===============
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the question num
SELECT
@columns+=QUOTENAME(Question_no) + ','
FROM
submission1_details WHERE Submission1_id=100
ORDER BY
Question_no;
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
SELECT Submission1_id,Name,Question_no,Answer FROM submission1_header t1 INNER JOIN submission1_details t2 ON t1.Submission1_id= t2.Submission1_id WHERE t1.Submission1_id=100
) t
PIVOT(
MAX(Answer)
for Question_no IN ('+ @columns +')
) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
Output:
Submission1_id NAME 1 2 3 4 5
100 Rahul YES YES NO NO YES
Now I want to join the third table too. And the output should be:
Submission1_id NAME sub1_Q1 sub1_Q2 sub1_Q3 sub1_Q4 sub1_Q5 sub2_Q1 sub2_Q2 sub2_Q3
100 Rahul YES YES NO NO YES YES YES
If the submission1_details table contains a maximum of 10 questions then the output should show the columns from sub1_Q1 to sub1_Q10.
Similarly if the submission2_details table contains a maximum of 20 questions then the output should create the columns from sub2_Q1 to sub2_Q20.
DECLARE
@columns NVARCHAR(MAX) = '',
@columnsname NVARCHAR(MAX) = '',
@columnsnameA NVARCHAR(MAX) = '',
@columnsB NVARCHAR(MAX) = '',
@columnsnameB NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the question num
SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar))
FROM submission1_details WHERE Submission1_id=100
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @columnsname = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub1_Q'+ cast(Question_no as varchar)
FROM submission1_details WHERE Submission1_id=100
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @columnsnameA = STUFF((SELECT distinct ','+' sub1_Q'+ cast(Question_no as varchar)
FROM submission1_details WHERE Submission1_id=100
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @columnsB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar))
FROM submission2_details WHERE Submission2_id=500
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @columnsnameB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub2_Q'+ cast(Question_no as varchar)
FROM submission2_details WHERE Submission2_id=500
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @columns
SET @sql ='
select Submission1_id,Submission2_id,Name ,'+ @columnsnameA+ ','+@columnsnameB +' from (
select Submission1_id,Submission2_id,Name ,'+@columnsname+',Question_no, Answer from (
select distinct t1.Submission1_id,t1.Submission2_id,Name,t2.Question_no t,t2.Answer A,t3.Question_no,t3.Answer from submission1_header t1
inner JOIN submission1_details t2 ON t1.Submission1_id= t2.Submission1_id
inner JOIN submission2_details t3 ON t1.Submission2_id= t3.Submission2_id
WHERE t1.Submission1_id=100
) as a
PIVOT(
MAX(A)
for t IN ('+ @columns +')
) AS pivot_table
) as b
PIVOT(
MAX(Answer)
for Question_no IN ('+ @columnsB +')
) AS pivot_table1;';
print @sql
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;