Use of wilcards in column lists

Transact-SQL
1

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.

Ed

2

Please provide:

  • table definition in the form of create statement
  • sample data in the form of insert statement
  • expected output for the sample data you provide
1 Like
3

In general you need something like:

Questionnaire

Q_ID
QuestionText
DataTypeOfTheResponse and any other stuff

ResponseHeader

RH_ID
UserID, DateFormCompleted, etc.

ResponseDetail

RH_ID - FKey to the ResponseHeader
QuestionID - FKey to the Questionnaire
SubItem - Normally 1, but 2,3,4,... when the question is allowed to be repeated
ResponseTextOrNumber

Is a given question, which can be answered multiple times, ALWAYS answered the SAME number of times by everyone? Or is it a "Tell me [ALL] the makes of cars you have ever driven?" type thing?

If the latter AND you want one-column-for-every-possible-repeat-number in your PIVOT table then I reckon that's going to be difficult ...

In that case I would look to display the results in something other than a 2-dimensional aggregates (i.e. PIVOT) grid

1 Like
4

bitsmed, thanks for the reply.

Please see the two statements below. For this set of data I'd like to see the query result replicate the attached image

CREATE TABLE [dbo].[TestTable](
	[formId] [varchar](20) NULL,
	[question] [varchar](20) NULL,
	[answer] [varchar](20) NULL
) ON [DATA]

GO


INSERT INTO [dbo].[TestTable]
           ([formId]
           ,[question]
           ,[answer])
     VALUES
           ('F101','Sampling Date','20-01-2016'),
		   ('F101','1 - Ticket ID','111'),
		   ('F101','4 - Ticket ID','123'),
		   ('F101','8 - Ticket ID','211'),
		   ('F102','Sampling Date','21-01-2016'),
		   ('F102','1 - Ticket ID','ABC'),
		   ('F103','Sampling Date','21-01-2016'),
		   ('F103','1 - Ticket ID','301'),
		   ('F103','9 - Ticket ID','302')
GO

.

5

Kristen, thanks for taking the time to reply.

The situation is the latter, there could be one instance of an answer or multiple and it will vary across forms. I don't want a column per answer but a row per unique pairs of answer. Hopefully my other reply should be more clear.

Ed

6

Is this what you're looking for:

select q.formid
      ,q.answer as ticketid
      ,a.answer as samplingdate
  from (select formid
              ,answer
         from dbo.testtable
        where question like '%Ticket ID'
       ) as q
       left outer join (select formid
                              ,answer
                          from dbo.testtable
                         where question not like '%Ticket ID'
                       ) as a
                    on a.formid=q.formid
;
1 Like
7

bitsmed,

Thank you, I had already managed to do it using joins but it was very slow to return results. For some reason your syntax works much quicker so will do the job I need it to do.

Your help is much appreciated, thank you.

Ed