SQL - query with transposition?

Question

SQL

Good afternoon, please tell me what query is needed for the following task:
There is a table like
A x 23 1/1/2011
B x 36 1/1/2011
C x 21 1/1/2011
D x 29 1/1/2011
A x 35 2/1/2011
B x 28 2/1/2011
C x 27 2/1/2011
D x 31 2/1/2011
A y 27 1/1/2011
B y 40 1/1/2011
C y 27 1/1/2011
D y 17 1/1/2011
A y 17 2/1/2011
B y 38 2/1/2011
C y 37 2/1/2011
D y 27 2/1/2011
Need table like
ABCD
1 /1/2011 50 76 48 46
2/1/2011 5266 64 58
That is, for a given date, for each object A,B,C,D, the sum (x+y) is taken. The data is presented in a table of a different form.
Thank you very much!

Answer 1

if I understood the task correctly, then this is how:
TRANSFORM sum([field_with_digits]) as Sum
SELECT field_with_DATE,
Group By field_with_DATE
PIVOT field_with_letter

Answer 2

Since you are working with a relational database, the set of columns (a,b,c,d) must be predefined.
Right off the bat I can suggest this way to solve your problem:

SELECT
  t.date,
  SUM(ta),
  SUM(tb)
FROM
  (
   SELECT `date`, cnt AS a, 0 AS b
   FROM
     `table`
   WHERE `name` = 'a'    
   UNION
   SELECT `date`, 0 AS a, cnt AS b
   FROM
     `table`
   WHERE `name` = 'b'
   ) t
GROUP BY t.date
        

Basically for c,d,e etc. you can pre-generate the request automatically. But such a request will not work very quickly. For some kind of admin panel where they go to see the report once a day, it’s normal. For a frequently visited page, perhaps it is worth revising the logic?

Answer 3

would this option not work?

Answer 4

You can use GROUP BY on two columns (date and object A,B,C,D)