How to retrieve data from a single column in one table with different conditions(dates)

Question

I have a table here:

Salesid   Total      Date
----------------------------
   1       100    2018-11-24
   2       200    2018-11-25
   3       300    2018-11-26
   4       400    2018-11-27
   5       500    2018-11-28

I'm having a difficulty retrieving separately each total for each day with assigned aliases and in a single query so it would appear like:

Day1   Day2   Day3   Day4   Day5
100    200    300    400    500

I have tried various queries but most of them yield an error

"Subquery returns multiple rows"

I think it is similar to this: https://stackoverflow.com/a/17591536/6351894 : `maskdetail = STUFF(sub_query)` — Ngoc Nam, Dec 03 '18 at 03:48

score 1 · Accepted Answer · answered Dec 03 '18 at 03:43

Consider the use of pivot if its available in the database you are using. Otherwise you can opt for a query structure as follows

select MAX(case when salesid=1 then total end) as day1
      ,MAX(case when salesid=2 then total end) as day2
      .....
  from table

How to retrieve data from a single column in one table with different conditions(dates)

1 Answers1