CODEWITHSUNDEEP
mysqlsqltranspose
Wenyi Yan
Wenyi Yan

Reputation: 63

How to transpose the table in SQL

Table abc: 

cookie_id   key         time
123         search      5/6/15
123         search      5/7/15
123         homepage    5/6/15
123         book        5/12/15
234         homepage    5/7/15
234         search      5/8/15

expected result as below : 

cookie_id   homepage    search     book
123          5/6/15     
123                     5/6/15  
123                     5/7/15  
123                               5/12/15
234          5/7/15     
234                     5/8/15

how to make expected result table with tableabc????

Upvotes: 1

Views: 31

Answers (2)

shA.t
shA.t

Reputation: 16958

Try something like this:

SELECT 
    `Cookie_id`,
    (CASE WHEN `key` = 'homepage' THEN `time` END) AS `homepage`,
    (CASE WHEN `key` = 'search' THEN `time` END) AS `search`,
    (CASE WHEN `key` = 'book' THEN `time` END) AS `book`
FROM
    `tableabc`

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use case statement to get the desired result.

SELECT cookie_id,
       CASE WHEN key = 'search' THEN time ELSE NULL END as search, -- You can replace NULL with empty string
       CASE WHEN key = 'homepage' THEN time ELSE NULL END as homepage,
       CASE WHEN key = 'book' THEN time ELSE NULL END as book
FROM   Yourtable

Upvotes: 1

Related Questions