2

i have a table which contain all values of items for all dates. table name is item_data 

╔═════════╦═══════╦═════════════╗
║ item_id ║ value ║ report_date ║
╠═════════╬═══════╬═════════════╣
║     101 ║    50 ║ 2014-10-11  ║
║     102 ║    50 ║ 2014-10-11  ║
║     101 ║   100 ║ 2014-10-12  ║
║     102 ║    11 ║ 2014-10-12  ║
╚═════════╩═══════╩═════════════╝

and i have another table which contain item names table name is page_items 

╔═════════╦═══════════╗
║ item_id ║ item_name ║
╠═════════╬═══════════╣
║     101 ║ sprite    ║
║     102 ║ egg       ║
╚═════════╩═══════════╝

my requirement is i want to display values as below 

╔═════════════╦════════╦═════╗
║ report_date ║ sprite ║ egg ║
╠═════════════╬════════╬═════╣
║ 2014-10-11  ║     50 ║  50 ║
║ 2014-10-12  ║    100 ║  11 ║
╚═════════════╩════════╩═════╝

can any one plz help me i am using mysql database please tell me the process to get this result

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
ArunKumar
  • 43
  • 1
  • 7

2 Answers2

1

You can use conditional aggregation max with case, grouping by the date:

select report_date,
    max(case when item_id = 101 then value end) sprite,
    max(case when item_id = 102 then value end) egg
from item_data
group by report_date
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • I have a *feeling* that number of items can vary. We, most likely, are looking at dynamic PIVOT. – PM 77-1 Dec 30 '14 at 04:42
  • thank you for giving me reply actually i have lot of item _id in item_data table i don't know item_id s how can i pass that item id in max case – ArunKumar Dec 30 '14 at 04:52
  • 1
    Oracle *got* MySQL as a part of [Sun Microsystems acquisition](http://en.wikipedia.org/wiki/MySQL#Legal_and_acquisition_impacts). – PM 77-1 Dec 30 '14 at 04:52
  • @PM77-1 -- Ok, I see what you're saying. I usually don't relate mysql to oracle though as that generally implies plsql, but in ownership, you are correct. – sgeddes Dec 30 '14 at 04:57
  • @ArunKumar -- that's a different question and would require `dynamic sql` to generate the results. Search SO for `mysql dynamic pivot` and you should find some guidance. Here's a good example: http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot – sgeddes Dec 30 '14 at 04:58
  • iam trying that but it will not gave exact result – ArunKumar Dec 30 '14 at 05:11
  • i want pass only single select command then i will get this result please help me – ArunKumar Dec 30 '14 at 05:23
  • JOIN is the best option. – Ataboy Josef Dec 30 '14 at 06:17
  • @sgeddes Your query is running correctly , can u please tell me why we need to use max in this query , i tried to run the query without max in that case for the second product results showing Null , but i am not able to understand how the command max solves this problem. – John Dec 30 '14 at 07:31
  • @John -- it's a form of table pivoting -- transposing multiple rows into a single row. Using `group by`, this creates a single row for each report_date. Then using conditional aggregation, we can set the individual column values. Other RDBMS support the `pivot` command, but mysql does not. – sgeddes Dec 30 '14 at 14:01
  • @usermesam0023 -- `join` will not solve the OP's problems. He'll need to use dynamic sql to create the list of potential columns, and then use conditional aggregation as in my example. Your post is inaccurate and won't run as is (missing a `case` statement). Either way, without using `max`, yours will return a random row per each report_date (http://sqlfiddle.com/#!2/8a805c/9)... – sgeddes Dec 30 '14 at 14:09
0

Try this:

select ID.`report_date`,
    case (when PI.`item_id` = 101 then PI.`value` end) AS sprite,
    (when PI.`item_id` = 102 then PI.`value` end) AS egg
from item_data ID
JOIN page_items PI (ON PI.`item_id` = ID.`item_id`)
group by ID.`report_date`
Ataboy Josef
  • 2,087
  • 3
  • 22
  • 27
  • thanks for ur reply but i have lot of dates and item ids in my table so how can i define item id s – ArunKumar Dec 30 '14 at 06:42
  • So you have to add an inner query instead of a simple case with 2 values. Is there any primary key field for your table 'item_data'? – Ataboy Josef Dec 30 '14 at 06:46
  • thanks again yaah i have primary key as item_data_id in item_data table – ArunKumar Dec 30 '14 at 06:54
  • can please tell me the process – ArunKumar Dec 30 '14 at 06:54
  • Okay, Here I give a try..as per your requirement, the first item is always `report_date`. Now the next column(s), they are always the DISTICT `item_name` values from table page_items. Now the values.. report_date is always from item_data. And you have to choose the curresponding `value` for the same field with same `item_id` in the other table. – Ataboy Josef Dec 30 '14 at 07:08
  • yaah exactly that is my requirement now you got exact idea please try to get that result – ArunKumar Dec 30 '14 at 07:20