Complex subquery - but looping?

Q&A DQ Courses
, , ,
#1

Hello everyone,

I have a bit of a dilemma. I want to do something similar to the below query, but a bit more advanced basically.

My table is structured like match_result, player_name, loser_name, tournament_name, round, etc.

I want to generate separate columns for the results of each tournament. e.g.

tournament_one_result column would have ( player_name || " " || “beat” || loser_Name || " " || etc… and then the round beat in a separate column.

But I need some way to do this for all tournaments. My first approach was to create each tournament in a separate subquery, e.g. WITH TOURNEY_ONE AS ( ___ ), but there’s over 50 tournaments so not practical.

Could anyone help me with this?

My Code: 

WITH 

country AS (SELECT customer_id, country, first_name, last_name FROM customer),

total_purchases AS (SELECT customer_id, SUM(total) AS total FROM invoice
                    GROUP BY customer_id)
                    
SELECT country.country AS country, (country.first_name || " " || country.last_name) AS customer_name, MAX(total_purchases.total) AS total_purchased
  FROM country 
INNER JOIN total_purchases ON total_purchases.customer_id = country.customer_id
GROUP BY 1
ORDER BY 1;
#2

Can you please share some example data and example output? Preferably the starting data will be in a SQLite database. This will help others help you.

#3

Hi Bruno, thanks for your reply.

I want the output to be like:

round tourney_one_result tourney_two_result tourney_three_result
Final player 1 beat player 2 player 2 beat player 3 player 5 beat player 3

etc…

the table is arranged currently with the player names for winners and losers in a separate column, and the result and round in a separate column, too.

Basically, using an approach like:

WITH tournament_one AS
(SELECT (matches.winner_name || " " || || "beat"|| ||" "||  matches.loser_name) AS result_one)

But the issue is that there’s so many tournaments so it isn’t practical to write each one as an individual subquery.

Would be very grateful for any assistance as I am very stuck!

Thanks

#4

For what it’s worth, I’m not at all inclined to try to help without input data I can play with it.

#5

Hi Bruno,

It’s this data here.

Thanks in advance if you have any insight on how to achieve what I am looking for.

test_data__atp_matches_2018.csv (641.9 KB)

#6

Thanks. So, if I understand correctly, you want a column for each value in tourney_id.

In this case, what you want isn’t possible with pure SQL unless you manually create columns for each id, which I think is not what you want.

SQL is not the right tool for this. It is possible to do this in some databases, but that’s a database specific feature, not a SQL thing.