SQL Results horizontally rather than vertically

Question

I have a table where I have a ticket_id's,field_id's and values. eg.

ticket_id   field_id    value
1001        1           Peter
1002        1           Dave
1001        2           555-2565
1002        2           554-2548

The goal is I am trying to write a query that will display values within the same table, horizontally. eg.

Ticket_ID     Field1       Field2
1001          Peter        555-2565
1002          Dave         554-2548

There are many field_ids. I have tried using the rownumber() but it didn't work.

Any ideas?

google for `mysql rank`, [this is one such](http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/) — tuxuday, Aug 22 '12 at 07:06

rshetye · Answer 1 · 2012-08-22T07:11:04.870

2

Select Temp_field1.ticket_id,Field1,Field2
From
    (Select ticket_id,value as Field1 from table where field_id = 1) as Temp_field1,
    (Select ticket_id,value as Field2 from table where field_id = 2) as Temp_field2
Where Temp_field1.ticket_id = Temp_field2.ticket_id;

edited Aug 22 '12 at 07:11

answered Aug 22 '12 at 07:01

rshetye

667
1
8
21

You're missing some `=` signs. – Barmar Aug 22 '12 at 07:03

score 1 · Answer 2 · edited Sep 07 '21 at 07:12

SELECT TICKET_ID, MAX(FIELD_1),MAX(FIELD_2)
FROM (
    SELECT TICKET_ID, 
        CASE field_id
            WHEN 1 THEN VALUE
            ELSE NULL end AS FIELD_1,
        CASE field_id
            WHEN 2 THEN VALUE
            ELSE NULL end AS FIELD_2
)
GROUP BY FIELD_1,FIELD_2

It's a little quirky, but i tried to minimize the selects. The idea is to put null when we don't need the value and when we do MAX, it wil take the value that is not null (MAX works on VARCHAR)

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

This answer was given before. You can check it here.

MySQL pivot table

Basically you have to pivot a "group"(or a distinct) select on your field_id.

edited May 23 '17 at 12:04

Community

1
1

answered Aug 22 '12 at 07:13

Dumitrescu Bogdan

7,127
2
23
31

score 0 · Answer 4 · answered Aug 22 '12 at 07:36

Here's one option. Don't know about the performance if there are many field_ids.

SELECT t1.`ticket_id` AS Ticket_ID, t1.`value` AS Field1, t2.`value` AS Field2 FROM `ticket` t1
  LEFT JOIN `ticket` t2 ON t1.`ticket_id` = t2.`ticket_id`
  WHERE t1.`value` != t2.`value`
  GROUP BY t1.`ticket_id`

SQL Results horizontally rather than vertically

4 Answers4