I am a very beginner  creating my first project and can't find the solution how to get results in a one row.

Below I pasted part of code: 3 fighters (Monika,Pawel,Tomasz) 2 styles_names (MT,K1), 2 fight_level (AM, PRO_AM). I doubt I created those tables in a proper way and I am looking for a better solution which allows to get all data selected in one row (for one fighter). I plan to send results to csv file so all data related to one fighter must be in one row for further sorting. I would be grateful for suggestions on how to solve it.

CREATE TABLE fighters ( fighter_id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,fighter_name VARCHAR(20));

CREATE TABLE styles (style_id INT UNIQUE,fight_style_name VARCHAR(4),fight_level VARCHAR (8));

CREATE TABLE styles_preferred (fighter_id INT,style_id1 INT,style_id2 INT,style_id3 INT,style_id4 INT,FOREIGN KEY(fighter_id) REFERENCES fighters(fighter_id) ON DELETE SET NULL,FOREIGN KEY(style_id1) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id2) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id3) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id4) REFERENCES styles(style_id) ON DELETE SET NULL);

CREATE TABLE styles_optional (fighter_id INT,style_id1 INT,style_id2 INT,style_id3 INT,style_id4 INT,FOREIGN KEY(fighter_id) REFERENCES fighters(fighter_id) ON DELETE SET NULL,FOREIGN KEY(style_id1) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id2) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id3) REFERENCES styles(style_id) ON DELETE SET NULL,FOREIGN KEY(style_id4) REFERENCES styles(style_id) ON DELETE SET NULL);

INSERT INTO fighters (fighter_name) VALUES ('Monika'),('Paweł'),('Tomasz);

INSERT INTO styles (style_id,fight_style_name,fight_level) VALUES(2001,'MT','AM'),(2002,'MT','PRO_AM'),(2003,'K1','AM'),(2004,'K1','PRO_AM');

INSERT INTO styles_preferred(fighter_id, style_id2, style_id4) VALUES(1, 2002,2004);

INSERT INTO styles_optional(fighter_id, style_id1 ) VALUES (1, 2001);

INSERT INTO styles_preferred(fighter_id, style_id3) VALUES (2, 2003);

INSERT INTO styles_optional(fighter_id, style_id1, style_id4) VALUES (2, 2001, 2004);

INSERT INTO styles_preferred(fighter_id, style_id1) VALUES (3, 2001);

SELECT (fighters.fighter_name, CONCAT (styles.fight_style_name, styles.fight_level))FROM fighters LEFT JOIN styles_preferred ON styles_preferred.fighter_id = fighters.fighter_id LEFT JOIN styles_optional ON styles_optional.fighter_id = fighters.fighter_id LEFT JOIN styles ON styles.style_id = styles_preferred .style_id2 OR styles.style_id = styles_preferred .style_id4 OR styles.style_id = styles_optional.style_id4 WHERE styles_preferred.style_id2 = 2002 OR styles_preferred.style_id4 = 2004 OR styles_optional. style_id4 = 2004;

That's what I've got:

That's what I would like to get:

CodePudding user response：

Your query is almost what you are looking to get. What you need is string_agg after the concatenating the style data. See Demo. Note, the delimiters (: and /) are my additions not something the query would have generated without my specifying them.

select (fighter_name, string_agg(styles, '/') )  
 from ( select
            fighters.fighter_name
             , CONCAT (
                styles.fight_style_name
                ,':'
                , styles.fight_level) styles
        from fighters
    left join styles_preferred on
        styles_preferred.fighter_id = fighters.fighter_id
    left join styles_optional on
        styles_optional.fighter_id = fighters.fighter_id
    left join styles on
        styles.style_id = styles_preferred .style_id2
        or styles.style_id = styles_preferred .style_id4
        or styles.style_id = styles_optional.style_id4
    where
        styles_preferred.style_id2 = 2002
        or styles_preferred.style_id4 = 2004
        or styles_optional. style_id4 = 2004 ) fs
group by fighter_name;
  

Side note: you need desperately to format your SQL. You posted a 540 character query in a single line, I surely hope this was just a mistake in pasting it. That is because a query requiring that that much right scrolling is virtually unreadable. Unfortunately your other statements suffer the same fate, just not as badly. The format above was generated by DBeaver's built in format function. Virtually all DB interfaces offer one, use them.