SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ArtistGenres (
 ID INT,
 Genre VARCHAR (30),
 Frequency DECIMAL(10,2)
);
INSERT INTO ArtistGenres
(ID, Genre, Frequency) VALUES
(1, 'Rock', 0.11),
(1, 'Punk', 0.22),
(1, 'Glam', 0.33),
(2, 'Metal', 0.44),
(2, 'Heavy Metal', 0.55),
(2, 'Thrash Metal', 0.66);
6 rows affected

WITH CTE AS
(
   SELECT ID, Genre, CAST(Frequency AS VARCHAR(30)) AS  Frequency,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Frequency) AS rn
   FROM ArtistGenres
)
SELECT *
FROM
(
     SELECT ID, CONCAT(ca.Name,' ',rn) AS Col, ca.Val
     FROM CTE
     CROSS APPLY (VALUES ('genre', Genre),('frequency', Frequency)) ca(Name, Val)      
) src
PIVOT
(
  MAX(Val)
  FOR Col IN ([genre 1], [frequency 1], [genre 2], [frequency 2], [genre 3], [frequency 3])
) pvt; 


ID
genre 1
frequency 1
genre 2
frequency 2
genre 3
frequency 3


1
Rock
0.11
Punk
0.22
Glam
0.33

2
Metal
0.44
Heavy Metal
0.55
Thrash Metal
0.66


SELECT ID,
MAX(CASE WHEN rn = 1 THEN Genre END) AS [genre 1],
MAX(CASE WHEN rn = 1 THEN Frequency END) AS [frequency 1],
MAX(CASE WHEN rn = 2 THEN Genre END) AS [genre 2],
MAX(CASE WHEN rn = 2 THEN Frequency END) AS [frequency 2],
MAX(CASE WHEN rn = 3 THEN Genre END) AS [genre 3],
MAX(CASE WHEN rn = 3 THEN Frequency END) AS [frequency 3]
FROM
(
   SELECT ID, Genre, Frequency, 
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Frequency) AS rn
   FROM ArtistGenres
) q
GROUP BY ID
ORDER BY ID;


ID
genre 1
frequency 1
genre 2
frequency 2
genre 3
frequency 3


1
Rock
0.11
Punk
0.22
Glam
0.33

2
Metal
0.44
Heavy Metal
0.55
Thrash Metal
0.66


Warning: Null value is eliminated by an aggregate or other SET operation.

ID	genre 1	frequency 1	genre 2	frequency 2	genre 3	frequency 3
1	Rock	0.11	Punk	0.22	Glam	0.33
2	Metal	0.44	Heavy Metal	0.55	Thrash Metal	0.66