How to write an sql query in which you may have to transpose data in a table?

Question

Oracle

One mmorpg database has three tables:
Players: players:
NUMBER id,
VARCHAR2 email
Characters: CHARACTERS:
NUMBER id,
NUMBER player_id,
NUMBER class_id
VARCHAR2 character_name
NUMBER character_level Character
classes: classes:
NUMBER id,
VARCHAR2 class_name
How to find the number of players who have there is a character class "Priest"?
3). Suppose in the previous mmorpg one player cannot have more than one character of each class. The list of classes, for example, is known: Priest, Mage, Warrior, Rogue.
You need to make a selection of emails of players and the names of their characters, broken down by class, for example
email Priest Mage Warrior Rogue
-------------------------------------------------- ----------------------
[email protected] Zinin Nafig Polzie
[email protected] Neo Demon Ironman Joker

Answer 1

-- Создаем таблички

USE [Test]

CREATE TABLE [players]
(id INT IDENTITY(1,1),
email NVARCHAR(100))

CREATE TABLE [classes]
(id INT IDENTITY(1,1),
class_name NVARCHAR(100))

CREATE TABLE [CHARACTERS]
(
id INT IDENTITY(1,1),
player_id INT ,
class_id INT,
character_name nvarchar(100),
character_level INT)


-- Заполняем данными

USE [Test]
GO

TRUNCATE TABLE [dbo].[players]
INSERT INTO [dbo].[players] ([email]) VALUES      ('Вася@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Петя@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Задрот@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Бот@mail.ru')


TRUNCATE TABLE [dbo].[classes]
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Priest')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Mage')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Warrior')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Rogue')


TRUNCATE TABLE [dbo].[CHARACTERS]

-- Первому игроку создаем одного персонажа
INSERT INTO [dbo].[CHARACTERS] ([player_id],[class_id],[character_name],[character_level])VALUES(1,1,'Вася_Priest',12)

-- Второму игроку создаем двух персонажей
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(2,1,'Петя_Priest',10)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(2,2,'Петя_Mage',11)

-- Третий игрок - три персонажа
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,1,'Задрот_Priest',21)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,2,'Задрот_Mage',22)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,3,'Задрот_Warrior',23)

-- Четвертый игрок
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,3,'Бот_Warrior',71)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,2,'Бот_Mage',72)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,3,'Бот_Warrior2',73)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,4,'Бот_Rogue',74)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,4,'Бот_Rogue2',85)



-- 1.Находим количество игроков у которых есть персонаж Priest

SELECT 
tmp.[Название класса] AS 'Название класса'
,COUNT (DISTINCT tmp.[ID игрока]) AS 'Количество игроков с искомым классом'
FROM (
SELECT cl.class_name AS 'Название класса'
,pl.id AS 'ID игрока'
, count (cl.id) OVER (PARTITION BY pl.id ORDER BY pl.id ) AS 'Количество персонажей у игрока'
 FROM players pl
JOIN CHARACTERS ch ON pl.id=ch.player_id
JOIN classes cl ON cl.id=ch.class_id

WHERE cl.class_name LIKE 'Priest' -- если закоментить эту строку то можно определить сколько игроков каждого класса 
) tmp

GROUP BY tmp.[Название класса]


-- 2. Выборка персонажей по емейлу

SELECT DISTINCT pl.email,
(
      SELECT 
      cl1.[class_name]  + ' ' AS [text()]
      FROM players pl1
      JOIN CHARACTERS ch1 ON pl1.id=ch1.player_id
      JOIN classes cl1 ON cl1.id=ch1.class_id
      WHERE pl.id=pl1.id
            For XML PATH ('')
        ) AS 'Классы игрока'
      FROM players pl
JOIN CHARACTERS ch ON pl.id=ch.player_id
JOIN classes cl ON cl.id=ch.class_id

Answer 2

Ideally, you should have a cached dictionary of classes with their identifiers and the most banal SELECT from WHERE to class_id will be done. If not, then a JOIN of the form is made

SELECT id, player_id, class_id ,character_name ,character_level FROM Characters as c
INNER JOIN classes as cl ON cl.id = c.class_id
WHERE condition

For the third point, ideally, you need a temp table that stores the necessary classes and a JOIN is done like

SELECT id, player_id, class_id ,character_name ,character_level FROM Characters as c
INNER JOIN classes as cl ON cl.id = c.class_id
INNER JOIN #temp as t ON cl.class_name = t.value
WHERE condition

Answer 3

If without temporary tables? Adhering to the structure, the output table is exactly specified in the task, so that the classes are separate columns for each email. How to do it?

Answer 4

Wouldn't it fit like this?

mysql> select email, group_concat(concat(character_name, ':', 
class_name, ' ',character_level,' lvl') separator ', ') as Chars 
from characters,players,classes 
where player_id=players.id and class_id=classes.id group by player_id;
+--------+--------------------------------------------+
| email  | Chars                                      |
+--------+--------------------------------------------+
| [email protected] | Merlin:wizard 70 lvl, Conan:warrior 80 lvl |
| [email protected] | Azariel:warrior 50 lvl, Bilbo:thief 20 lvl |
+--------+--------------------------------------------+
2 rows in set (0.00 sec)

in ORacle, instead of group_concat, use LISTAGG
If you need to separately display a separate class in each column, then change select by adding an expression separated by a comma similar to the output for Chars, but using, for example, CASE where you compare via WHEN with the required class.

Answer 5

Try with PIVOT, haven't tested:

select *
from
(
  select 
    p.email,
    cr.character_name,
    cs.class_name
  from players p
  inner join characters cr on cr.player_id = p.id
  inner join classes cs on cs.id = cr.class_id
)
pivot
(      
 MAX(character_name)
  for class_name in ('Priest' as 	"Priest", 
                'Mage' as 	"Mage", 
                'Warrior' as "Warrior", 
                'Rogue' as "Rogue")
)

And with DECODE:

select 
    p.email,
 decode(class_name, 'Priest', character_name, null)  as "Priest",
 decode(class_name, 'Mage', character_name, null)  as "Mage",
 decode(class_name, 'Warrior', character_name, null)  as "Warrior",
 decode(class_name, 'Rogue', character_name, null)  as "Rogue"
  from players p
  inner join characters cr on cr.player_id = p.id
  inner join classes cs on cs.id = cr.class_id

Answer 6

1) How to find the number of players who have a "Priest" class character?
It is stupid to rewrite this phrase in SQL:
Select * from player where exists (select * from character inter join class on character class_id = class_id where character. Player_id=player.id)
2) here you need to know how the classes are known, this can be their names or customized table with their id options - either pivot or n subqueries or just group characters table by players and classes and transpose on render