Is there a MySql SELECT that will give a column A vs column B result?

Question

In this question... How to represent X and Y axis data in an SQL table ...the poster asks how to create a MySql table that represents an Excel spreadsheet with x vs y type information.

I'm wondering if there is any SELECT that would give a result that looks like the original Excel format of column x vs column y? In other words, how to start with the table given in the answer and get back to the spreadsheet shown in the question.

Modified problem 1: Suppose the source MySql table does not have a unique mapping of x to y, and instead of a result with the value for x vs y, you want a table that shows the COUNT('value') for each x vs y combination. (i.e. a histogram for x vs y samples)

Modified problem 2: Same as modified problem 1, except that there are some combinations of x and y that don't exist in the MySql table, so you want the count to show as 0 in the SELECT result. (i.e. a histogram for x vs y samples, where not every combination of x vs y is in the table)

Here is an example of what I would like to achieve for "Modified problem 2":

   x    y  value                           y 635 762 889 
----------------                        x    ------------
 762  635      1                        762   3   4   1
 762  635    100        ->             1016   1   0   1
 762  635      7                       1270   1   1   2
 762  762      9
 762  762      9
 762  762      9
 762  762      9
 762  889      2
1016  635      1
1016  889      3
1270  635      2
1270  762      3
1270  889      3
1270  889      4

I am able to create a select with GROUP BY that will give me:

   x    y  COUNT(value)
------------------- 
 762  635      3
 762  762      4
 762  889      1
1016  635      1
1016  889      1
1270  635      1
1270  762      1
1270  889      2

But it would more convenient for me if a single query could make the 2-D style table. I'd also like to account for the missing combination x=1016, y=762. I've gotten sort of close with GROUP_CONCAT(), but I end up with:

   x | hist
----------
 762 | 3,4,1
1016 | 1,1
1270 | 1,1,2

It would be nice to have the y values as column headers, but that isn't the end of the world. But the missing x,y combination is a non-starter. My real data is a much bigger histogram, and not knowing where the NULLs are is no good.

My current MySql skills are not up to the task. Or, maybe, it just cannot be done and I should stop trying. :D Hoping someone knows the answer. Thanks in advance!

Thanks @Strawberry! That is good advice! I'll try to do better next time. — bnoronb, Jun 12 '21 at 01:44

score 0 · Answer 1 · answered Jun 11 '21 at 05:34

Use my stored procedure from MySQL pivot tables - rows to colums . Query. Prepare the data for pivotting in a view:

CREATE VIEW source_for_pivot
AS 
SELECT x, y, COUNT(value) cnt
FROM test
GROUP BY x, y;

then simply

CALL pivot ('source_for_pivot', 'x', 'y', 'cnt');

fiddle

If complete list of y values is static, never-changed and well known, then you may find/use more simple solutions.

Is there a MySql SELECT that will give a column A vs column B result?

1 Answers1