pythonsqlitegroup-bycaseconditional-aggregation

Use multiple INNER JOINS to transpose one column in multiple columns

I have the following table

CREATE TABLE "holes" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "stimp" INTEGER,
);

With the following small sample of data:

33  2016    895 1   1   12
33  2016    895 1   2   18
33  2016    895 1   3   15
33  2016    895 1   4   11
33  2016    895 1   5   18
33  2016    895 1   6   28
33  2016    895 1   7   21
33  2016    895 1   8   14
33  2016    895 1   9   10
33  2016    895 1   10  11
33  2016    895 1   11   12
33  2016    895 1   12   18
33  2016    895 1   13   15
33  2016    895 1   14   11
33  2016    895 1   15   18
33  2016    895 1   16   28 
33  2016    895 1   17   21
33  2016    895 1   18   14

The goal is to show each hole as a column. At the moment I am using this query but it's very slow.

SELECT h.tournament, h.year, h.course, h.round, 
hole1.stimp AS "hole 1", 
hole2.stimp AS "hole 2",
hole3.stimp AS "hole 3",
hole4.stimp AS "hole 4", 
hole5.stimp AS "hole 5",
hole6.stimp AS "hole 6", 
hole7.stimp AS "hole 7",
hole8.stimp AS "hole 8", 
hole9.stimp AS "hole 9", 
hole10.stimp AS "hole 10",
hole11.stimp AS "hole 11",
hole12.stimp AS "hole 12", 
hole13.stimp AS "hole 13",
hole14.stimp AS "hole 14", 
hole15.stimp AS "hole 15", 
hole16.stimp AS "hole 16", 
hole17.stimp AS "hole 17", 
hole18.stimp AS "hole 18"
FROM holes h
INNER JOIN holes hole1
ON hole1.course = h.hole
AND hole1.hole = '1'
INNER JOIN holes hole2
ON hole2.course = h.hole
AND hole2.hole = '2'
INNER JOIN holes hole3
ON hole3.course = h.hole
AND hole3.hole = '3'
INNER JOIN holes hole4
ON hole4.course = h.hole
AND hole4.hole = '4'
INNER JOIN holes hole5
ON hole5.course = h.hole
AND hole5.hole = '5'
INNER JOIN holes hole6
ON hole6.course = h.hole
AND hole6.hole = '6'
INNER JOIN holes hole7
ON hole7.course = h.hole
AND hole7.hole = '7'
INNER JOIN holes hole8
ON hole8.course = h.hole
AND hole8.hole = '8'
INNER JOIN holes hole9
ON hole9.course = h.hole
AND hole9.hole = '9'
INNER JOIN holes hole10
ON hole10.course = h.hole
AND hole10.hole = '10'
INNER JOIN holes hole11
ON hole11.course = h.hole
AND hole11.hole = '11'
INNER JOIN holes hole12
ON hole12.course = h.hole
AND hole12.hole = '12'
INNER JOIN holes hole13
ON hole13.course = h.hole
AND hole13.hole = '13'
INNER JOIN holes hole14
ON hole14.course = h.hole
AND hole14.hole = '14'
INNER JOIN holes hole15
ON hole15.course = h.hole
AND hole15.hole = '15'
INNER JOIN holes hole16
ON hole16.course = h.hole
AND hole16.hole = '16'
INNER JOIN holes hole17
ON hole17.course = h.hole
AND hole17.hole = '17'
INNER JOIN holes hole18
ON hole18.course = h.hole
AND hole18.hole = '18'
GROUP BY h.tournament, h.year, h.course, h.round

Please advice!

The suggestion of @Parfait looks as follows

SELECT h.tournament, h.year, h.course, h.round, 
    MIN(CASE WHEN h2.hole = '1' THEN h2.stimp END) AS "hole 1", 
    MIN(CASE WHEN h2.hole = '2' THEN h2.stimp END) AS "hole 2",
    MIN(CASE WHEN h2.hole = '3' THEN h2.stimp END) AS "hole 3",
    MIN(CASE WHEN h2.hole = '4' THEN h2.stimp END) AS "hole 4",
    MIN(CASE WHEN h2.hole = '5' THEN h2.stimp END) AS "hole 5",
    MIN(CASE WHEN h2.hole = '6' THEN h2.stimp END) AS "hole 6",
    MIN(CASE WHEN h2.hole = '7' THEN h2.stimp END) AS "hole 7",
    MIN(CASE WHEN h2.hole = '8' THEN h2.stimp END) AS "hole 8",
    MIN(CASE WHEN h2.hole = '9' THEN h2.stimp END) AS "hole 9",
    MIN(CASE WHEN h2.hole = '10' THEN h2.stimp END) AS "hole 10",
    MIN(CASE WHEN h2.hole = '11' THEN h2.stimp END) AS "hole 11",
    MIN(CASE WHEN h2.hole = '12' THEN h2.stimp END) AS "hole 12",
    MIN(CASE WHEN h2.hole = '13' THEN h2.stimp END) AS "hole 13",
    MIN(CASE WHEN h2.hole = '14' THEN h2.stimp END) AS "hole 14", 
    MIN(CASE WHEN h2.hole = '15' THEN h2.stimp END) AS "hole 15",
    MIN(CASE WHEN h2.hole = '16' THEN h2.stimp END) AS "hole 16",
    MIN(CASE WHEN h2.hole = '17' THEN h2.stimp END) AS "hole 17",
    MIN(CASE WHEN h2.hole = '18' THEN h2.stimp END) AS "hole 18"
FROM holes h
INNER JOIN holes h2
   ON h2.course = h.hole
GROUP BY h.tournament, h.year, h.course, h.round

I replaced MAX with MIN because there are some blanks and the output only shows these using MAX.enter image description here

Solution

  • You don't need joins.

    Use conditional aggregation:

    SELECT tournament, year, course, round, 
       MAX(CASE WHEN hole = 1 THEN stimp END) AS "hole 1", 
       MAX(CASE WHEN hole = 2 THEN stimp END) AS "hole 2",
       MAX(CASE WHEN hole = 3 THEN stimp END) AS "hole 3",
       MAX(CASE WHEN hole = 4 THEN stimp END) AS "hole 4",
       MAX(CASE WHEN hole = 5 THEN stimp END) AS "hole 5",
       MAX(CASE WHEN hole = 6 THEN stimp END) AS "hole 6",
       MAX(CASE WHEN hole = 7 THEN stimp END) AS "hole 7",
       MAX(CASE WHEN hole = 8 THEN stimp END) AS "hole 8",
       MAX(CASE WHEN hole = 9 THEN stimp END) AS "hole 9",
       MAX(CASE WHEN hole = 10 THEN stimp END) AS "hole 10",
       MAX(CASE WHEN hole = 11 THEN stimp END) AS "hole 11",
       MAX(CASE WHEN hole = 12 THEN stimp END) AS "hole 12",
       MAX(CASE WHEN hole = 13 THEN stimp END) AS "hole 13",
       MAX(CASE WHEN hole = 14 THEN stimp END) AS "hole 14", 
       MAX(CASE WHEN hole = 15 THEN stimp END) AS "hole 15",
       MAX(CASE WHEN hole = 16 THEN stimp END) AS "hole 16",
       MAX(CASE WHEN hole = 17 THEN stimp END) AS "hole 17",
       MAX(CASE WHEN hole = 18 THEN stimp END) AS "hole 18"
FROM holes
GROUP BY tournament, year, course, round;

    See the demo.