MySQL Divide the two columns into two different rows.

Question

I have no idea to create this sorry if this is a stupid question.

I have a table with two commands and a general shift, and I will use this information later in another place, so my idea is to combine two columns in one column, but in two different rows:

HomeTeam AwayTeam Totalwatch AB 100 AC 90 CA 80 DB 70 CE 50 

Can i have this

 Teams TotalWatch A 100 B 100 A 90 C 90 C 80 A 80 D 70 B 70 C 50 E 50 

I have several columns to make them repeat.

Just a note that I know how concat use concat on a single line using the concat function I don’t know how I can do with two lines

Answer 1

You can use UNION ALL and ORDER BY Totalwatch DESC to get results ordered by Totalwatch .

 SELECT HomeTeam AS Teams, Totalwatch FROM YourTable UNION ALL SELECT AwayTeam, Totalwatch FROM YourTable ORDER BY Totalwatch DESC; 

Answer 2

Just use UNION ALL :

 SELECT * FROM( SELECT HomeTeam Teams,TotalWatch FROM Your_Table UNION ALL SELECT AwayTeam,TotalWatch FROM Your_Table )D ORDER BY TotalWatch DESC 

Answer 3

Try this bro .. :)

 SELECT HomeTeam,Totalwatch FROM YourTable UNION ALL SELECT AwayTeam,Totalwatch FROM YourTable