Complex SQL Pivot query

Question

A quick background so that my problem makes sense: The system collects data from the user in the form of questionnaires. Users belong to Organisations, Organisations belong to Sectors, and Questions/Calculations (as found on the questionnaires) differ across the Sectors. (Questions are answered by users ; Calculations are calculated by the system).

The following tables exist:

Sectors (SectorID, Name)
Organisations (OrganisationID, Name, SectorID)
Years (YearID, Name)

Questions (QuestionID, DisplayText, CommonName, SectorID)
Answers (AnswerID, Answer, OrganisationID, YearID, QuestionID)

Calculations (CalculationID, DisplayText, CommonName, SectorID)
CalculationResults (CalculationResultID, Result, OrganisationID, YearID, CalculationID)

I need to display data in the following way:

The thing that makes this particularly complex (for me) is that questions are displayed (to the user) in different ways across the different sectors that they belong to, but some of them can still be common questions. E.g. "Manufacturing sales" would be the same thing as "Sales (manufacturing)". I need to be using the CommonName field to determine commonality.

I've managed to use SQL Pivot to get close to what I want - SQL Fiddle (if you run the SQL you'll notice the nulls and the "commonality" issue). However some things are missing from my attempt:

1. Commonality and column names - I need the column names to be the CommonName field, not the QuestionID field.

2. I've only selected from the Answers table - I need to also select from the CalculationResults table which is identically structured.

Edit: Desired result with the SQL Fiddle data is: (The two blocks with the orange corners need to shift all the way to the left, so that there are a total of 3 columns for the Questions - the 3 unique CommonName values. The next 3 columns are for the 3 unique CommonName values for Calculations. I hope I've made sense, if not let me know.)

Edit2: Another edit just for fun. I've definitely thought about redesigning the db but it's not an option at this stage - too risky on this legacy system. In case anyone saw the design and thought that. I need a solution in the form of Pivot hopefully.

Answer 1

Sometimes instead of PIVOT you can use [Aggregate](CASE EXPRESSION) to get the same data. And sometimes it's faster.

For your problem you can use OUTER APPLY with dynamic MAX(CASE)

DECLARE @Questions NVARCHAR(MAX),
        @Calculations NVARCHAR(MAX),
        @Sql NVARCHAR(MAX)

SELECT  @Questions = COALESCE(@Questions + ', ', '') 
            + 'MAX(CASE WHEN q.CommonName = ''' + CommonName + ''' THEN a.Answer END) AS ' + QUOTENAME(CommonName)
FROM    Questions 
GROUP BY CommonName

SELECT  @Calculations = COALESCE(@Calculations + ', ', '') 
            + 'MAX(CASE WHEN c.CommonName = ''' + CommonName + ''' THEN cr.Result END) AS ' + QUOTENAME(CommonName)
FROM    Calculations  
GROUP BY CommonName

SET     @Sql = N'
        SELECT 
            o.Name As [Organisation],
            y.Name As [Year],
            q.*,
            c.*
        FROM 
            Organisations o
            CROSS JOIN Years y
            OUTER APPLY (
                SELECT ' + @Questions + ' 
                FROM    Answers a 
                        JOIN Questions q ON a.QuestionID = q.QuestionID
                WHERE   a.OrganisationID = o.OrganisationID 
                        AND a.YearID = y.YearID
            ) q
            OUTER APPLY (
                SELECT ' + @Calculations + ' 
                FROM    CalculationResults cr 
                        JOIN Calculations c ON cr.CalculationID = c.CalculationID
                WHERE   cr.OrganisationID = o.OrganisationID 
                        AND cr.YearID = y.YearID
            ) c
'

SQL FIDDLE DEMO

Answer 2

Basically we want to get the order of the QuestionID Grouped By SectorID, and Name.

You can do this using PARTITION BY with something like this:

ROW_NUMBER() OVER(PARTITION BY q.SectorID, y.Name ORDER BY a.QuestionID)

this should do it:

    DECLARE @cols AS  NVARCHAR(MAX)
     , @query AS NVARCHAR(MAX);

SELECT                     @cols = STUFF(
                                 (SELECT DISTINCT
                                        ','+QUOTENAME(CAST(ROW_NUMBER() OVER(PARTITION BY q.SectorID
                                                                              , y.Name ORDER BY a.QuestionID) AS VARCHAR(10)))
                                  FROM   Answers a
                                        LEFT JOIN Years y ON a.YearID = y.YearID
                                        LEFT JOIN Organisations o ON a.OrganisationID = o.OrganisationID
                                        LEFT JOIN Questions q ON a.QuestionID = q.QuestionID
                                  FOR XML PATH(''), TYPE).value
      ('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = '
            SELECT Organisation, Year, '+@cols+' from 
(
 SELECT QuestionID = ROW_NUMBER() OVER(PARTITION BY q.SectorID
                                              , y.Name ORDER BY a.QuestionID)
           , o.Name AS Organisation
           , y.Name AS Year
           , a.Answer
       FROM   Answers a
            LEFT JOIN Years y ON a.YearID = y.YearID
            LEFT JOIN Organisations o ON a.OrganisationID = o.OrganisationID
            LEFT JOIN Questions q ON a.QuestionID = q.QuestionID
) src
pivot
(
  max(Answer)
  for QuestionID in ('+@cols+')
) piv
order by Organisation, Year
';

PRINT(@query);

EXECUTE (@query);

RESULT: