Hemant Hemant - 2 months ago 26
SQL Question

SQL Aggregate value in column datewise

Below is my database values.

enter image description here

I am looking for sum of values in column header datewise and I have MS Access database. Can anyone help me to build SQL query?
Please find below expected output.

enter image description here

Answer

If you know the set of dates, then you can use conditional aggregation. In MS Access this uses the iif() function. Your dates look like strings, so:

select a, b, c, d,
       sum(iif(store_date = '17-09-2016', val, 0)) as [17-09-2016],
       sum(iif(store_date = '18-09-2016', val, 0)) as [18-09-2016],
       sum(iif(store_date = '19-09-2016', val, 0)) as [19-09-2016],
       sum(iif(store_date = '20-09-2016', val, 0)) as [20-09-2016]
From t
Group by a, b, c, d;

You should store dates in the database using the built-in date data type. However, if you must store them as strings, I would strongly encourage you to use the ISO standard YYYY-MM-DD format. This is useful because you can use it for ordering and for range comparisons.