Home:ALL Converter>In SQL, query a table by transposing column results

In SQL, query a table by transposing column results

Ask Time:2021-09-22T05:58:26         Author:logjammin

Json Formatter

Background

Forgive the title of this question, as I'm not really sure how to describe what I'm trying to do.

I have a SQL table, d, that looks like this:

+--+---+------------+------------+
|id|sex|event_type_1|event_type_2|
+--+---+------------+------------+
|a |m  |1           |1           |
|b |f  |0           |1           |
|c |f  |1           |0           |
|d |m  |0           |1           |
+--+---+------------+------------+

The Problem

I'm trying to write a query that yields the following summary of counts of event_type_1 and event_type_2 cut (grouped?) by sex:

+-------------+-----+-----+
|             |  m  |  f  |
+-------------+-----+-----+
|event_type_1 |  1  |  1  |
+-------------+-----+-----+
|event_type_2 |  2  |  1  |
+-------------+-----+-----+

The thing is, this seems to involve some kind of transposition of the 2 event_type columns into rows of the query result that I'm not familiar with as a novice SQL user.

What I've tried

I've so far come up with the following query:

SELECT event_type_1, event_type_2, count(sex)
FROM d
group by event_type_1, event_type_2

But that only gives me this:

+------------+------------+-----+
|event_type_1|event_type_2|count|
+------------+------------+-----+
|1           |1           |1    |
|1           |0           |1    |
|0           |1           |2    |
+------------+------------+-----+

Author:logjammin,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/69275978/in-sql-query-a-table-by-transposing-column-results
yy