CODEWITHSUNDEEP
mysqlsqlpivot
Jon Evans
Jon Evans

Reputation: 53

SQL query to pivot table on column value

This has probably been covered before, but my skill level with SQL is low enough that I'm not even sure how to properly search for what I want!

I think what I want to do is relatively simple, and it seems that using pivoting might be the solution, but I'm really not sure about the syntax for that.

I have a SELECT query which can return data in this format:

 TeamID |   PlayerName
--------+-------------------
   1    | Arthur Schiller
   1    | Dimitre Rogatchev
   1    | Mohamed Hechmeh
   1    | Santosh Pradhan
   2    | Adriano Ferrari
   2    | Amanda Compagnone
   2    | Danail Delchev
   2    | David Bergin

I want to create columns from the ID with rows filled with the appropriate names, like this:

        1         |         2
------------------+-------------------
 Arthur Schiller  | Adriano Ferrari
 Dimitre Rogatchev| Amanda Compagnone
 Mohamed Hechmeh  | Danail Delchev
 Santosh Pradhan  | David Bergin

The purpose is to use the returned data in a php mysqli_fetch_assoc call to display the two columns on a website.

Thanks in advance for any help!

Upvotes: 0

Views: 378

Answers (3)

Taryn
Taryn

Reputation: 247810

Unfortunately MySQL doesn't support windowing functions to generate a unique value for each of those rows per team. You can create a derived "row number" using variables or you could use a correlated subquery similar to:

select 
  max(case when teamid = 1 then playername else '' end) Team1,
  max(case when teamid = 2 then playername else '' end) Team2
from
(
  select TeamId,
    PlayerName,
    (select count(*)
     from yourtable d
     where t.teamId = d.TeamId
       and t.playername <= d.PlayerName) rn
  from yourtable t
) d
group by rn;

See SQL Fiddle with Demo.

Note: depending on the size of your data you might have some performance issues. This works great with smaller datasets.

Upvotes: 2

Tim B
Tim B

Reputation: 41208

This will be much easier to do in the PHP code rather than trying to do it in the SQL. Just perform the query and then loop through the results adding them to an array for each team.

In general SQL is good for storing, associating, indexing and querying.

Massaging it into the right format you need to display is usually easier, cleaner and neater to do in code though.

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25862

you cannot do this with a pivot because there is no common ground to pivot off of.. what you can do is make a row count that you join on for the second team.. try this

SELECT t.playername as '1', f.playername as '2'
FROM
(   SELECT @a := @a + 1 as id, playername 
    FROM players 
    WHERE teamid = 1
)t
LEFT JOIN 
(   SELECT @b := @b +1 as id , playername 
    FROM players 
    WHERE teamid = 2
)f ON f.id = t.id
CROSS JOIN (SELECT @a :=0, @b :=0)t1

DEMO

Upvotes: 2

Related Questions