First post from a novice user so please bear with me.
I have a database where each line contains a form id, a question and an answer from an online form. I'm trying to create a query that lists all of the form id in the first column and then subsequent columns for each question. I can do this where the question is unique. Due to the way the form works it is possible to answer a question multiple times. In this instance the question in the database is prefixed with an identifier. This identifier then forms part of the question text. The various instances of the forms may have any number of instances of the question being answered.
In my example the question that can exist multiple times is 'Ticket ID'
My code so far is as below
SELECT formId AS [FormId], [%Ticket ID] AS [Ticket ID], [Sampling Date] AS [Actual Date] FROM (SELECT [formId], question, CASE WHEN answer IN ('', 'N/A') THEN NULL ELSE answer END AS answer FROM [dbo].[FV_FormQA_Copy] WHERE formName = 'BYLOR - Concrete Delivery & Sampling Form' AND projectid = '3399') AS SourceTable PIVOT (MIN(answer) FOR question IN ([%Ticket ID], [Sampling Date])) AS PivotTable
As you can see I've tried to use the wildcard but the result is it looks for a question called exactly '%Ticket ID' and returns nulls.
How do I use wildcards in this situation to create a line per instance of Ticket ID question? For clarity the identifiers are not consistent, they are a randomly generated character.
Any and all help gratefully received.