SQL Pivot Syntax

Question

I want something that looks like this:

Group | ID  | Date   | Time    | Phone Number | How tired are you? | How happy are you? | 
  1   | A23 | 1/1/12 | 5:30:00 |   8001231234 |         5          |         8          |

However, I'm getting this:

Group | ID  | Date   | Time    | Phone Number |     Question       |  Answer | 
  1   | A23 | 1/1/12 | 5:30:00 |   8001231234 | How tired are you? |     5   |
  1   | A23 | 1/1/12 | 5:30:00 |   8001231234 | How happy are you? |     8   |

I've looked up a lot of possible solutions and know that I have to use Pivot for cases like this. However, I can't get the syntax to work. Below is my current code:

SELECT
CASE when a.send_time between '2012-1-1 00:00:00' and '2012-1-2 23:59:59' then 1
    else 2
    end as "group",
u.id AS ID,
cast(a.send_time as date) AS "Date",
cast(a.send_time as time) AS "Time",
u.cellphone AS "Phone Number",
i.question AS "Question",
a.answer AS "Answer"
FROM
   answer a, option o, box b, item i, user u
WHERE
   a.id = b.id and
   a.item_id = i.item_id and
   o.item_id = a.item_id and
   o.value = a.answer and
   u.id = a.user_id;

I'm using MySQL. Thank you!!!

@barmar I found a lot of pivot table questions but I don't understand how the syntax is supposed to be like. Most examples only have the pivoted columns, selecting from a single table, and NO where clause. — Dao Lam, Jun 11 '13 at 06:00
Adding pivoted columns to a query doesn't generally change those parts of the query. — Barmar, Jun 11 '13 at 06:02
@barmar I tried to follow the syntax here http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx but don't know what to put for `[first pivoted column] AS ` because I don't ahve the column name. Column name is supposed to be my "question". Also, I don't know what to put in FROM. Do I do something like: `FROM answer a, option o, box b, item i, user u, ` `SELECT( ... ) AS sourceTable` `PIVOT ( ... ) AS pivotTable` Not sure what to put in the FOR either... I played around with it for hours but didn't work. — Dao Lam, Jun 11 '13 at 06:10
MySQL doesn't have `PIVOT()`, that's why you have to do something like peterm's answer. — Barmar, Jun 11 '13 at 06:12
Do you have to solve this in MySQL? Are you performing the query from a programming language, so you can rearrange the data for display there? — Barmar, Jun 11 '13 at 06:15

peterm · Accepted Answer · 2013-06-11T07:03:24.450

3

Try

SELECT q.`Group`, q.`ID`, q.`Date`, q.`Time`, q.`Phone Number`, 
    MIN(CASE WHEN Question = 'How tired are you?' THEN Answer ELSE NULL END) `How tired are you?`,
    MIN(CASE WHEN Question = 'How happy are you?' THEN Answer ELSE NULL END) `How happy are you?`
FROM 
(
SELECT
      CASE when a.send_time between '2012-1-1 00:00:00' AND '2012-1-2 23:59:59' then 1
      ELSE 2 END as `group`,
      u.id AS ID,
      cast(a.send_time as date) AS `Date`,
      cast(a.send_time as time) AS `Time`,
      u.cellphone AS `Phone Number`,
      i.question AS `Question`,
      a.answer AS `Answer`
FROM
   answer a, option o, box b, item i, user u
WHERE
   a.id = b.id and
   a.item_id = i.item_id and
   o.item_id = a.item_id and
   o.value = a.answer and
   u.id = a.user_id;
) q
GROUP BY q.`Group`, q.`ID`, q.`Date`, q.`Time`, q.`Phone Number`

edited Jun 11 '13 at 07:03

answered Jun 11 '13 at 05:53

peterm

91,357
15
148
157

Thank you for your answer. But the above code is only a simplified version of my SQL code. There are actually like >100 questions so I want to use Pivot to make the code cleaner and less copy&paste – Dao Lam Jun 11 '13 at 06:02
1

@DaoLam You could've mentioned that in the question. There is no `PIVOT` in MySql. You'll have to resort to dynamic SQL. More over I can't imagine someone reading such output if you have >100 questions. If on the other hand you doing this for presentation layer IMHO you should drop it and do it using client side programming language of your choosing (e.g. php). – peterm Jun 11 '13 at 06:13
I don't really have a client side. They just want an excel sheet in that format... Thanks though! And is the keyword "MIN" necessary? – Dao Lam Jun 11 '13 at 06:29
Since `GROUP BY` is used, an aggregate function should be applied. In your particular case `MIN()` or `MAX()`. If you drop `MIN()` (MySql's group by extensions will allow you that) you'll get one row with the second question's answer set to `NULL`. – peterm Jun 11 '13 at 06:35
Thank you! That makes sense. I tested your code and it works, except that I had to make some small changes. I'm going to post it here in case some other people need to do the same thing In the first SELECT, change it to `SELECT q.Group, q.ID, q.Date, q.Time, q.Phone_Number` same as `GROUP BY` – Dao Lam Jun 11 '13 at 06:51
@DaoLam You are more than welcome :) Good luck. – peterm Jun 11 '13 at 07:03

SQL Pivot Syntax

1 Answers1