select * transform row value as table column name

Question

I am trying to transform row value as column name. After searching on stackoverflow I learned that it can be done by using GROUP_CONCAT(). I tried it but no result.

what I want??

i have a table like this :

id  |    staff_id_staff     |   leave_type_id_leave_type    |    days
1   |    41                 |   Casual                      |    7
2   |    41                 |   Earned                      |    1
3   |    41                 |   Sick                        |    4

and want result like this:

Casual    |    Earned   |   Sick
7         |       1     |   4

Please note: I dont know the value of leave_type_id_leave_type (it will be anything)

Here is the code of leave_remain table:

CREATE TABLE IF NOT EXISTS `leave_remain` (
  `id_leave_remain` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `days` float DEFAULT NULL,
  `updated` date DEFAULT NULL,
  PRIMARY KEY (`id_leave_remain`),
  UNIQUE KEY `leave_type_id_leave_type_UNIQUE` (`leave_type_id_leave_type`),
  KEY `fk_leave_remain_staff1` (`staff_id_staff`),
  KEY `fk_leave_remain_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=108 ;

--
-- Dumping data for table `leave_remain`
--

INSERT INTO `leave_remain` (`id_leave_remain`, `staff_id_staff`, `leave_type_id_leave_type`, `days`, `updated`) VALUES
(82, 41, 16, 16, '2013-02-04'),
(89, 41, 17, 178, '2013-02-06'),
(107, 41, 18, 0, '2013-02-04');

If you don't know the potential values for leave_type_id_leave_type, then you'll probably need to use Dynamic SQL. See this post: http://stackoverflow.com/a/12005676/1073631 — sgeddes, Feb 08 '13 at 04:51

Minesh · Accepted Answer · 2013-02-08T08:54:20.970

3

See you need to work on something similar to this query:

SELECT GROUP_CONCAT(CONVERT(leave_type_id_leave_type,char(10)))
FROM leave_remain
GROUP BY staff_id_staff
UNION
SELECT GROUP_CONCAT(CONVERT(days,char(10)))
FROM leave_remain
GROUP BY staff_id_staff

Check demo on SqlFiddle

edited Feb 08 '13 at 08:54

answered Feb 08 '13 at 05:52

Minesh

2,284
1
14
22

I got this result after executing : `[BLOB - 2B] [BLOB - 2B] [BLOB - 1B]` – user007 Feb 08 '13 at 08:30
@user1983017 Just above the query result (to the left) you will see +options. Press it and mark Show BLOB contents I have made some correction so it should work anyway, check now – Minesh Feb 08 '13 at 08:54

score 1 · Answer 2 · answered Feb 08 '13 at 05:51

1

Try the below code

SELECT 
max(DECODE(leave_type_id_leave_type,'Casual',days)) Casual,
max(DECODE(leave_type_id_leave_type,'Earned',days)) Earned,
max(DECODE(leave_type_id_leave_type,'Sick',days)) Sick
FROM table_name;

answered Feb 08 '13 at 05:51

Madhav

2,285
3
17
16

Raul · Answer 3 · 2013-02-08T05:55:38.423

-1

  select leave_type_id_leave_type,days,  
count(case when leave_type_id_leave_type = 'Casual' THEN 1 END) Casual,
count(case when leave_type_id_leave_type = 'Earned' THEN 1 END) Earned,
count(case when leave_type_id_leave_type = 'Sick' THEN 1 END) Sick 
from leave_remain GROUP BY id_leave_remain

See SqlFiddle

edited Feb 08 '13 at 05:55

answered Feb 08 '13 at 05:09

Raul

579
1
5
17

sorry initially i did not create any schema just wrote a sample code. no i think it working – Raul Feb 08 '13 at 05:54

select * transform row value as table column name

3 Answers3

Linked