0

I have a table that logs interactions from users with the following structure.

SELECT [SessionID]
      ,[Question]
      ,[Response]
      ,[DateCreated]
      ,[Terminal]
FROM [Quiz]
where Teminal in ('A1','A2','B1')

Output:

SessionID Question  Response  DateCreated              Terminal
--------- --------- --------- -----------------------  -------
123       Q1        A         2020-02-16 10:10:15.200  A1
123       Q2        A         2020-02-16 10:10:15.560  A1
123       Q3        B         2020-02-16 10:10:16.120  A1
124       Q1        B         2020-02-16 16:01:34.205  A2
124       Q2        B         2020-02-16 16:01:38.999  A2
124       Q3        A         2020-02-16 16:01:44.435  A2
125       Q1        D         2020-02-16 16:02:01.013  B1
126       Q1        A         2020-02-17 07:49:55.098  B1
126       Q2        A         2020-02-17 07:49:57.101  B1

A user reports they had issues during their interaction "the other day" but cant tell me the date or time, only it was one of those 'Terminals' (worked out to be A1, A2 or B1).

They also know they answered 'A' for 'Question 1' and also 'A' for 'Question 2'.

Is there away I can query the original 'Quiz' table (without creating a temporary table) and end up with an output something like the following that collates the response to each question into a related column on the one row per SessionID?

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz

Output:

SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
124        B      2020-02-16 16:01:34.205  B      2020-02-16 16:01:38.999  A      2020-02-16 16:01:44.435  A2
125        D      2020-02-16 16:02:01.013  null   null                     null   null                     B1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1

Or

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz
WHERE Q1 = 'A'
  and Q2 = 'A'


SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1
1

Maybe you need

SELECT session_id,
       MAX(CASE WHEN question = 'Q1' then responce END) q1,
       MAX(CASE WHEN question = 'Q1' then DateCreated END) q1_datetime,
       MAX(CASE WHEN question = 'Q2' then responce END) q2,
       MAX(CASE WHEN question = 'Q2' then DateCreated END) q2_datetime,
       MAX(CASE WHEN question = 'Q3' then responce END) q3,
       MAX(CASE WHEN question = 'Q3' then DateCreated END) q3_datetime,
       terminal
FROM quiz
WHERE terminal IN ('A1','A2','B1')
GROUP BY session_id, terminal
-- HAVING MAX(CASE WHEN Question = 'Q1' then Response END) = 'A' 
--    AND MAX(CASE WHEN Question = 'Q2' then Response END) = 'B';
| improve this answer | |
  • this was able to give me the output format i wanted, however i can't seem to add any condition/where on the alias columns. When I add WHERE Q1 = 'A' it gives error Invalid column name 'Q1'. The same error occurs if I instead use HAVING Q1 = 'A'. – Durry42 Feb 27 at 6:57
  • @Durry42 The same error occurs if I instead use HAVING Q1 = 'A'. May not. Please provide a fiddle with sample data and query attempt which results this error. – Akina Feb 27 at 7:08
  • here is the fiddle link – Durry42 Feb 28 at 3:28
  • @Durry42 Oops, this is MS SQL issue (I use MySQL mostly, it understands the reference to the output field by its name) - use the copy of the field expression instead of field name: fiddle. – Akina Feb 28 at 4:57
  • Thanks... that works great now! I've modified you answer above to include the working HAVING clause. – Durry42 Feb 28 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.