CODEWITHSUNDEEP
mysqlsqlsqlite
absessive
absessive

Reputation: 1171

How to combine 2 columns into one and use that merged column to hold multiple values

I have a table with columns

id, playername, sport1, sport2

However, my requirements say that a player can have n number of sports, so a multi-value column 'sports' is needed or else I would be going with sport3, sport4, and so on. How do I alter the table to combine sport1, sport2 and use that as a multi-value column for new rows going forward?

Upvotes: 0

Views: 136

Answers (2)

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

sqlite does not support multivalued fields. The only DBMS I am aware of that can do this is Microsoft Access but I wouldn't, in general, recommend switching from sqlite to Access, even if you are guaranteed to be running on Windows computers only.

This is a classic problem of database design. I recommend you switch from a single table to multiple tables so that each entity type is accurately portrayed in the database. For example, the name of a sport is not an attribute of a player, it is an attribute of a sport, so you probably need a Sport table. You clearly need a Player table. And since players can play multiple sports, and each sport might be played by multiple players, you have a many-to-many relationship. This can be modelled in a RDBMS using multiple tables with foreign keys.

Consider the following database structure (think of this as pseudo-code though it may work as is).

CREATE TABLE Player (
    PlayerId int NOT NULL PRIMARY KEY,
    Name string NOT NULL UNIQUE
)

CREATE TABLE Sport (
    SportId int NOT NULL PRIMAEY KEY,
    Name string NOT NULL UNIQUE
)

CREATE TABLE PlayerSport (
    PlayerId int NOT NULL FOREIGN KEY REFERENCES Player (PlayerId),
    SportId int NOT NULL FOREIGN KEY REFERENCES Sport (SportId),
    PRIMARY KEY (
        PlayerId,
        SportId
    )
)

This structure comes about via a process of database normalization.

You can now get a list of all sports for a player,

SELECT
    Sport.Name
FROM Sport
    INNER JOIN PlayerSport ON PlayerSport.SportId = Sport.SportId
        AND PlayerSport.PlayerId = @PlayerId
ORDER BY
    Sport.Name

Or all players for a sport,

SELECT
    Player.Name
FROM Player
    INNER JOIN PlayerSport ON PlayerSport.SportId = Player.PlayerId
        AND PlayerSport.SportId = @SportId
ORDER BY
    Player.Name

Or all players and sports,

SELECT
    Player.Name AS Player,
    Sport.Name AS Sport
FROM Player
    INNER JOIN PlayerSport ON PlayerSport.SportId = Player.PlayerId
    INNER JOIN Sport ON Sport.SportId = PlayerSport.SportId
ORDER BY
    Player.Name,
    Sport.Name

But note that the structure of this final query is not the same as your original table. To output a row per player and a column per sport is not possible in static SQL. You could construct a dynamic query but this is not easy, is error prone, and fallible to sql-injection security breaches.

Upvotes: 0

nurdglaw
nurdglaw

Reputation: 2127

The classic solution is to have a second table with columns for the playerId and the sport. Then, to see all the sports a player has, join the two.

Upvotes: 4

Related Questions