Joining SQL Tables and display columns as rows to generate a report

Question

i have three tables related to each other explained below

Contacts
====================================================================================
ContactID           ContactName

1               Contact 1               
2               Contact 2
3               Contact 3
4               Contact 4
5               Contact 5


Questions
=====================================================================================
QuestionID          QuestionText

1               What is your Department?
2               Would you be interested in attending this year show?
3               Number of Employees in your company?


Answers
======================================================================================
QuestionID          ContactID           AnswerText

1                   1                   IT
1                   2                   HR
3                   4                   60
2                   2                   Yes

i would like to generate a report in below format

Report
====================================================================================================================================================

ContactID   What is your Department?    Would you be interested in attending this year show?        Number of Employees in your company?


1           IT                  NULL                                NULL
2           HR                  Yes                             NULL
4           NULL                    NULL                                60

is there a way to generate a dynamic report using T-SQL, I tried using pivot but most of the samples on the internet is using only one table and limited fields. any help or right direction would be appreciated.

Answer 1

You can use this query:

-- you columns
Declare @cols nvarchar(max);
Set @cols = STUFF((Select distinct ', ' + QUOTENAME(QuestionText) 
            From #Questions FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- dynamic query
Declare @sql nvarchar(max);
Set @sql = '
    Select * 
    From (
        Select a.ContactID, a.AnswerText, q.QuestionText From #Questions q
        Inner Join #Answers a On a.QuestionID = q.QuestionID
    ) as x
    Pivot (
        MAX(AnswerText)
        For QuestionText in ('+@cols+')
    ) as piv
';

-- execute the dynamic query
--print @sql;
Exec sp_executesql @sql;

When you write a Pivot query, it is best to use a subquery (X here) or a CTE. This will be used to join your data and return only the 3 rows required by the pivot.

@cols is used to get a list of your columns: [Number of Employees in your company?], [What is your Department?], [Would you be interested in attending this year show?]

This is added to the @sql variable. It contains the main query which will be executed with sp_executesql.

Output:

ContactID   What is your Department?    Would you be interested in attending this year show?    Number of Employees in your company?
1           IT                          NULL                                                    NULL
2           HR                          Yes                                                     NULL
4           NULL                        NULL                                                    60

Your data:

Declare table #Questions(QuestionID int, QuestionText varchar(100))
Insert into @Questions(QuestionID, QuestionText) values
    (1, 'What is your Department?')
    , (2, 'Would you be interested in attending this year show?')
    , (3, 'Number of Employees in your company?')

Declare table #Answers(QuestionID int, ContactID int, AnswerText varchar(10))
Insert into @Answers(QuestionID, ContactID, AnswerText) values 
    (1, 1, 'IT')
    , (1, 2, 'HR')
    , (3, 4, '60')
    , (2, 2, 'Yes')

Answer 2

I have created the column names are dynamically with the help of this link. Added the in-line comments for the queries

-- Declare Variables
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

-- Get distinct values of the PIVOT Column, here QuestionText
SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(QuestionText)
FROM (SELECT DISTINCT QuestionText FROM #Questions) AS QText
-- SELECT @ColumnName

SET @DynamicPivotQuery = N' 
SELECT * 
FROM (
    SELECT ANS.ContactID, ANS.AnswerText, QUE.QuestionText
    FROM #Questions QUE
    INNER JOIN #Answers ANS ON ANS.QuestionID = QUE.QuestionID
) AS T
PIVOT (
    MAX(AnswerText)
    FOR QuestionText IN (' + @ColumnName + ')
) AS PVTTable'

-- Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

The temporary table creations are:

CREATE  TABLE #Contacts(ContactID INT, ContactName VARCHAR (50))
CREATE  TABLE #Questions( QuestionID INT, QuestionText VARCHAR(100))
CREATE  TABLE #Answers(QuestionID INT, ContactID INT, AnswerText VARCHAR (100))

INSERT INTO #Contacts
SELECT 1, 'Contact 1' UNION SELECT             
2, 'Contact 2' UNION SELECT
3, 'Contact 3' UNION SELECT
4, 'Contact 4' UNION SELECT
5, 'Contact 5'

INSERT INTO #Questions
SELECT 1 , 'What is your Department?' UNION SELECT 
2 , 'Would you be interested in attending this year show?' UNION SELECT 
3 , 'Number of Employees in your company?' 

INSERT INTO #Answers
SELECT 1, 1 , 'IT' UNION SELECT  
1, 2 , 'HR' UNION SELECT  
3, 4 , '60' UNION SELECT  
2, 2 , 'Yes'

DROP The temporary tables:

DROP TABLE #Answers
DROP TABLE #Contacts
DROP TABLE #Questions