Help wanted: Special case of changing columns and rows MySQL Im beginner in MySQL and want to convert this table



Any help? Thanks
You could make it with CASE and UNION, but only if your Field column has fixed values:

SELECT 
    T2.Field,
    T2.S,
    T2.N,
    T2.NA,
    T2.P 
FROM (SELECT
            'C1' AS Field,
            SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C2' AS Field,
            SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C3' AS Field,
            SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'Cn' AS Field,
            SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T) AS T2;


Test:

mysql&gt; SELECT  T2.Field,         T2.S,         T2.N,         T2.NA,         T2.P      FROM (SELECT 'C1' AS Field, SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C2' AS Field, SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C3' AS Field, SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'Cn' AS Field, SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T) AS T2;
+-------+------+------+------+------+
| Field | S    | N    | NA   | P    |
+-------+------+------+------+------+
| C1    |    1 |    1 |    1 |    1 |
| C2    |    2 |    1 |    0 |    1 |
| C3    |    3 |    1 |    0 |    0 |
| Cn    |    0 |    3 |    1 |    0 |
+-------+------+------+------+------+
4 rows in set (0.00 sec)

mysql&gt;