Adjust your SQL queries to see data series

Many users have data in a specific table format with a 'type' column and want to show inside a chart series for every value of 'type'.

Lets take a simple example of a DAY table (where NAME column is our 'type') :

NAME               VALUE               CREATION_DATE
----------------------------------------------------------------- 
MORNING            90                  2015-06-12
NOON               210                 2015-06-12
EVENING            60                  2015-06-12
MORNING            85                  2015-06-13
NOON               240                 2015-06-13
EVENING            50                  2015-06-13
MORNING            92                  2015-06-14
NOON               235                 2015-06-14
EVENING            55                  2015-06-14

We want to show DATE on x axis and series for MORNING, NOON, EVENING on y axis. To make this happen NextReports must obtain a query result like:

CREATION_DATE     MORNING_V    NOON_V   EVENING_V
-------------------------------------------------------------------------------------------------
2015-06-12        90           210      60 
2015-06-13        85           240      50   
2015-06-14        92           235      55 

Then it is very easy to add DATE on x axis and MORNING_V, NOON_V, EVENING_V on y axis as column series.

This transformation can be easily obtained using sub-queries. For example, on our table we will use following sql:

SELECT DISTINCT
   a1.CREATIONDATE,
   (select VALUE from DAY a2 where a2.name='MORNING' and         
              a2.CREATIONDATE=a1.CREATIONDATE) as MORNING_V,
   (select VALUE from DAY a2 where a2.name='NOON' and
              a2.CREATIONDATE=a1.CREATIONDATE) as NOON_V,
   (select VALUE from DAY a2 where a2.name='EVENING' and
              a2.CREATIONDATE=a1.CREATIONDATE) as EVENING_V
FROM
DAY a1