mysql display multiple rows in one row

Question

I have a table tbl_usi in mysql with records as below:

present_date    usi_value    deal_count    
----------------------------------------------------------
2015-10-13      b1              c1           
2015-10-12      b2              c2             
2015-10-11      b3              c3

I want to write a query that will do this using present_date field to select the present date and the date before it and display them together:

present_date    usi_value    deal_count    previous_date   previous_usi_value   previous_deal_count       
----------------------------------------------------------
2015-10-13      b1              c1         2015-10-12      b2                   c2         
2015-10-12      b2              c2         2015-10-11      b3                   c3          
2015-10-11      b3              c3         2015-10-10      b4                   c4

How do I achieve this. Thanks

Similar question: stackoverflow.com/questions/11509407/…

user5226582
– user5226582

2015-10-14 12:42:14 +00:00
Commented Oct 14, 2015 at 12:42 — user5226582
– user5226582, Commented Oct 14, 2015 at 12:42

Michael · Accepted Answer · 2015-10-14 12:59:22Z

2

Select everything from your table, then join it to itself, making sure the 2 joined tables are given different names so you can distinguish them (I used 'a' and 'b' here). The join offsets the dates by 1 day. Then you can select the fields you want from the joined table.

select 
  a.present_date, 
  a.usi_value, 
  a.deal_count, 
  b.present_date as previous_present_date, 
  b.usi_value as previous_usi_value, 
  b.deal_count as previous_deal_count 
from
  tbl_usi as a 
  left join tbl_usi as b
    on b.present_date = a.present_date - interval 1 day;

If you didn't already have one before, you will now want an index for the present_date column too BTW.

edited Oct 14, 2015 at 12:59

answered Oct 14, 2015 at 12:45

Michael

3,74017 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gyne Over a year ago

Thanks a lot Michael. Your solution retrieves everything in a single row as expected but I now realised that null values are retrieved wherever the previous date is a weekend or public holiday of which no data will be in the table for that date. Is it possible for interval date or any other solution to retrieve the previous date by just off-setting the present_date by 1 row. This way there will be no null rows. I can then order by present_date. Yes I already have a unique key index on present_date. Thanks in Advance

Gyne Over a year ago

Thanks once again @Michael but I have resolved it now. I created an Auto increment ID field and then used it in the join by off-setting the previous_date by value 1. Here is the query I used. SELECT c.present_date PresentDate, c.usi current_usi, c.deals current_deals, c.volume current_volume, c.value current_value, c.capitalisation current_capitalisation,p.present_date previous_date, p.usi previous_usi, p.deals previous_deals, p.volume previous_volume, p.value previous_value, p.capitalisation previous_capitalisation FROM usi c LEFT JOIN usi p ON p.ID = c.ID-1 ORDER BY c.ID DESC

Michael Over a year ago

Yep fair enough, your question didn't state the data was consecutive, and my answer made an assumption. Although you have a solution now, I have posted another answer, for the community benefit, that answers your question, working by date rather than ID... The ID based solution I'm sure works fine for you, and the subquery solution is going to be slower, but of course using ID like this might cause problems later on - it too assumes a uniform consecutive progression of IDs...

Michael · Accepted Answer · 2015-10-14 18:46:00Z

0

Alternative, which works when there are date gaps.

select
  a.present_date, 
  a.usi_value, 
  a.deal_count, 
  b.present_date as previous_present_date, 
  b.usi_value as previous_usi_value, 
  b.deal_count as previous_deal_count 
from
  tbl_usi as a 
  join tbl_usi as b
where
  b.present_date = (select max(present_date) from tbl_usi where present_date < a.present_date);

As with previous solution the same table is joined twice, but this time the previous row is found by way of a subquery.

answered Oct 14, 2015 at 18:46

Michael

3,74017 silver badges31 bronze badges

1 Comment

Gyne Over a year ago

This works great @Michael . I actually tried it before but got stock at the WHERE clause. Thanks for this (where present_date < a.present_date);

Collectives™ on Stack Overflow

mysql display multiple rows in one row

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related