Use multiple INNER JOINS to transpose one column in multiple columns-CodePudding

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!

CodePudding user response：

Consider one self-join for course/hole pairing, then run conditional aggregation where you move ON conditions to CASE statements before reducing rows to groupings.

SELECT h.tournament, h.year, h.course, h.round, 
    MAX(CASE WHEN h2.hole = '1' THEN h2.stimp END) AS "hole 1", 
    MAX(CASE WHEN h2.hole = '2' THEN h2.stimp END) AS "hole 2",
    MAX(CASE WHEN h2.hole = '3' THEN h2.stimp END) AS "hole 3",
    ...
    MAX(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 h.tournament, h.year, h.course, h.round