SQL select and take data as column name

Question

I have a database which store records like:

+----+---------------------+-------------+-----------------+
| id | user_name           |       status|            date |
+----+---------------------+-------------+-----------------+
|  1 | A                   |         Paid|       2016-10-11|
|  2 | B                   |     Not Paid|       2016-10-12|
|  3 | C                   |         Paid|       2016-10-12|
|  4 | A                   |     Not Paid|       2016-10-13|
+----+---------------------+-------------+-----------------+

I wish to obtain the results like:

+----+---------------------+-------------+-----------------+-----------------+
| id | user_name           |   2016-10-11|      2016-10-12 |      2016-10-13 |
+----+---------------------+-------------+-----------------+-----------------+
|  1 | A                   |         Paid|               NA|         Not Paid|
|  2 | B                   |           NA|         Not Paid|               NA|
|  3 | C                   |           NA|             Paid|               Na|
+----+---------------------+-------------+-----------------+-----------------+

How can I query it to obtain the results like this?
PS: Poor English

FYI: I'm using mySQL as as DBMS and here is the create script:

CREATE TABLE `moneygame`.`pay_table`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(50),
  `status` VARCHAR(50),
  `p_date` DATE,
  PRIMARY KEY (`id`)
);

It's not a good idea to generate dynamic columns. If the user_names are fixed, you should rather have user names in columns and dates in records. — MontyPython, Oct 11 '16 at 10:50
Also, please always include the create script for these tables. — MontyPython, Oct 11 '16 at 10:51
@Java - Keep in mind that whenever you ask a question you need to add all the relevant information to get a proper answer. In that first and foremost is you should tell `DBMS` you are using — Pரதீப், Oct 11 '16 at 10:53

score 0 · Answer 1 · answered Oct 11 '16 at 11:03

0

You can query like this

select user_name, max([2016-10-11]) as [2016-10-11], max([2016-10-12]) [2016-10-12],max([2016-10-13]) [2016-10-13] from #yourtable 
pivot 
(max(status) for date in ([2016-10-11],[2016-10-12],[2016-10-13])) p
group by user_name

answered Oct 11 '16 at 11:03

Kannan Kandasamy

13,405
3
25
38

MySQL doesn't have pivot. You're probably using Oracle. – MontyPython Jan 31 '17 at 04:14

score 0 · Accepted Answer · edited May 23 '17 at 12:13

If don't have a fixed number of dates then I wouldn't recommend doing what you are trying to do. Anyways, here's the solution to the problem.

create table p as select * from
(select 1 id, 'A' user_name, 'Paid' status, '2016-10-11' date union 
select 2 id, 'B' user_name, 'Not Paid' status, '2016-10-12' date union 
select 3 id, 'C' user_name, 'Paid' status, '2016-10-12' date union 
select 4 id, 'A' user_name, 'Not Paid' status, '2016-10-13' date) t;

When dates are represented as columns

select user_name, ifnull(max(a),'NA') `2016-10-11`, 
ifnull(max(b),'NA') `2016-10-12`, ifnull(max(c),'NA') `2016-10-13`
from (select user_name, 
case when date = '2016-10-11' then status else null end a,
case when date = '2016-10-12' then status else null end b,
case when date = '2016-10-13' then status else null end c
from p group by user_name, date) s group by user_name;

When user names are represented as columns

Doing it this way should be optimal if you have a fixed number of users and an moving range of dates.

select date, ifnull(max(a),'NA') A, 
ifnull(max(b),'NA') B, ifnull(max(c),'NA') C from
(select date,
case when user_name = 'A' then status else null end a,
case when user_name = 'B' then status else null end b,
case when user_name = 'C' then status else null end c
from p group by date, user_name) x group by date;

By the way if you still need to do dynamic columns, you should read this:

what if the user and date will keep on adding? – Java Oct 12 '16 at 05:16 — Java, Oct 12 '16 at 05:16

SQL select and take data as column name

2 Answers2