Chris Chris - 1 year ago 66
SQL Question

Pivoting Undefined Text Columns

I have 2 tables of data that I need to join together in a SQL Server query - one with transaction information, and one with marketing questions that have been answered for a transaction.

Here is a stripped-down example:

| Transaction Info |
|Transaction_ID |Date |Customer_ID|
|1 |2016-01-01 |2614 |
|2 |2016-04-16 |3981 |
|3 |2016-06-25 |2113 |

| Marketing Questions |
|Transaction_ID |Question |Answer |
|1 |How would you rate our service? |Excellent |
|2 |Would you recommend us? |Yes |
|2 |Where did you hear about us? |Friend |
|1 |Any other comments? |None |
|3 |How would you rate our service? |Average |
|2 |Any other comments? |None |

Here's where I'm stuck: I need to pivot the questions in the marketing table to become column names in the final joined table, where the cell value is the answer.

The problem is that the questions are randomized from a very large set, and so I cannot define them in the query. It needs to dynamically capture all the questions that have been returned in the table, generate them as columns, and populate the answers in the cells.

The marketing table is itself the result of a date-ranged query, and so the questions included will not always be the same. This is why I can't just define them in a PIVOT ahead of time.

I really don't know where to start with this, so any help is greatly appreciated!

Answer Source

You can easily achieve this using dynamic SQL. For Example:

DECLARE @dynPiv varchar(max)
DECLARE @PivotQuery

SET @dynPiv = STUFF((SELECT ',' + QUOTENAME(Question)
                    FROM MarketingQuestions
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 

SET @PivotQuery = '[YOUR QUERY HERE]
PIVOT ( [YOUR PIVOT GOES HERE] FOR Question IN ' + @dynPiv + ')'

Didnt type out the whole code but that should communitcate the idea. The @dynPiv will take the value of all distinct values for Question. Print @dynPiv for a better visualization