use row values as columns

Question

Possible Duplicate:
MySQL pivot row into dynamic number of columns

I have a query that shows number of items per day

$query = "SELECT uploadedby, count(item) as item, date_format(uploaddate, '%m-%d-%y') as date FROM `imsexport` GROUP BY uploadedby,date";
$result = mysql_query($query);

The resulting table looks like this

   Name        item    date
Abel Antonio    10   01-02-12
Abel Antonio    5    01-03-12
Abel Antonio    6    01-04-12
Abel Antonio    2    01-05-12
Abel Antonio    1    01-09-12
Abel Antonio    15   01-12-12
Abel Antonio    22   01-16-12
Abel Antonio    15   01-17-12
Abel Antonio    7    01-19-12
Abel Antonio    45   02-15-12
Abel Antonio    31   02-16-12
...other names

I'd like to make the the dates as columns to make the query look like this

Name          01-02-12  01-03-12   01-04-12   01-05-12    01-09-12 ....
Abel Antonio    10         5          6          2           1     .....

Anyone know how to accomplish this?

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

So I think you would like to do a Pivot using a Dynamic number of columns. It depends what database you are using, but I'm guessing by the date_format statement that it is MYSQL. Here is a link to an answer that does what you want - enjoy!

https://stackoverflow.com/a/12005676/345659

Your code should look like this:

$query = "
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('count(case when date_format(uploaddate, ''%m-%d-%y'') = ''',
      date_format(uploaddate, '%m-%d-%y'),
      ''' then 1 end) AS `',
      date_format(uploaddate, '%m-%d-%y'), '`'
    )
  ) INTO @sql
from imsexport;

SET @sql = CONCAT('SELECT uploadedby AS Name, ', 
                  @sql, 
                  ' FROM imsexport 
                    GROUP BY Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";

$result = mysql_query($query);

I've used this data in the Fiddle provided by bluefeet in the linked question:

create table imsexport
(
  uniqueID int,
  item varchar(10),
  uploadedby varchar(50),
  uploaddate datetime
);
insert into imsexport values (1, 'Product A', 'Abel Antonio', '2013-01-01');
insert into imsexport values (2, 'Product B', 'Abel Antonio', '2013-01-01');
insert into imsexport values (3, 'Product C', 'Abel Antonio', '2013-01-01');
insert into imsexport values (4, 'Product A', 'Abel Antonio', '2013-01-01');
insert into imsexport values (5, 'Product A', 'Abel Antonio', '2012-01-01');
insert into imsexport values (6, 'Product A', 'Abel Antonio', '2012-01-01');
insert into imsexport values (7, 'Product A', 'Abel Antonio', '2012-01-01');

it give me this error when I run it in mysql - Error SQL query: PREPARE stmt FROM @sql ; — rain, Jan 09 '13 at 10:50
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM imsexport GROUP BY Name' at line 1 — rain, Jan 09 '13 at 11:02
Hi I think the code is giving me an error(You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM imsexport GROUP BY Name' at line 1), could it be because there are some name values that are null in certain dates? How could I fix that. Also how could I limit the dates to a certain range? My db already has records upto 2 years. Thank you so much for your help. — rain, Jan 23 '13 at 07:04
Can you use the fiddle in the link above to test with the data I've shown above? http://sqlfiddle.com/#!2/9618d/9 — JumpingJezza, Jan 24 '13 at 08:59

score 0 · Answer 2 · answered Jan 09 '13 at 07:49

0

Handle the display logic at the application level.

answered Jan 09 '13 at 07:49

Strawberry

33,750
13
40
57

use row values as columns

2 Answers2