SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SkillQuiz(
  [Coder] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Score] INT
)

INSERT INTO SkillQuiz VALUES
('Bob','SQL',95),
('Bob','Python',80),
('Emily','SQL',98),
('Emily','Python',90)
4 rows affected

SELECT *
FROM SkillQuiz


Coder
Subject
Score


Bob
SQL
95

Bob
Python
80

Emily
SQL
98

Emily
Python
90


SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Subject] IN (
	Python,SQL
			)) AS PivotTable


Coder
Python
SQL


Bob
80
95

Emily
90
98


SELECT DISTINCT (Subject)
FROM SKillQuiz


Subject


Python

SQL


SELECT STRING_AGG(SUB.Subject, ',') AS PivotList
FROM (
	SELECT DISTINCT (Subject)
	FROM SKillQuiz
	) AS SUB


PivotList


Python,SQL


CREATE PROCEDURE dbo.PivotRowsToColumnsDynamically
  @PivotColumn NVARCHAR(100),
  @PivotList NVARCHAR(255)
AS
BEGIN

DECLARE @PivotSQL NVARCHAR(MAX) = N'
SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [' + @PivotColumn + '] IN (
	' + @PivotList + '
			)) AS PivotTable
';

SELECT(@PivotSQL) AS PivotSQL;
	  
EXEC(@PivotSQL);

END
DECLARE @ColumnToPivot NVARCHAR(100) = 'Subject';
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Subject, ',')
		FROM (
			SELECT DISTINCT (Subject)
			FROM SKillQuiz
			) AS SUB
		);
		
EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;


PivotSQL



SELECT *
FROM (
 SELECT [Coder]
  ,[Subject]
  ,[Score]
 FROM SkillQuiz
 ) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Subject] IN (
 Python,SQL
   )) AS PivotTable





Coder
Python
SQL


Bob
80
95

Emily
90
98


INSERT INTO SkillQuiz VALUES
('Bob','Java',96),
('Bob','Scala',86),
('Bob','R',86),
('Emily','Java',91),
('Emily','Scala',91),
('Emily','R',86);

SELECT *
FROM SkillQuiz


Coder
Subject
Score


Bob
SQL
95

Bob
Python
80

Emily
SQL
98

Emily
Python
90

Bob
Java
96

Bob
Scala
86

Bob
R
86

Emily
Java
91

Emily
Scala
91

Emily
R
86


DECLARE @ColumnToPivot NVARCHAR(100) = 'Subject';
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Subject, ',')
		FROM (
			SELECT DISTINCT (Subject)
			FROM SKillQuiz
			) AS SUB
		);

EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;


PivotSQL



SELECT *
FROM (
 SELECT [Coder]
  ,[Subject]
  ,[Score]
 FROM SkillQuiz
 ) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Subject] IN (
 Java,Python,R,Scala,SQL
   )) AS PivotTable





Coder
Java
Python
R
Scala
SQL


Bob
96
80
86
86
95

Emily
91
90
86
91
98


DECLARE @ColumnToPivot NVARCHAR(100) = 'Coder';
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Coder, ',')
		FROM (
			SELECT DISTINCT (Coder)
			FROM SKillQuiz
			) AS SUB
		);
		
EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;		


PivotSQL



SELECT *
FROM (
 SELECT [Coder]
  ,[Subject]
  ,[Score]
 FROM SkillQuiz
 ) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Coder] IN (
 Bob,Emily
   )) AS PivotTable





Subject
Bob
Emily


Java
96
91

Python
80
90

R
86
86

Scala
86
91

SQL
95
98
Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Emily	SQL	98
Emily	Python	90
Bob	Java	96
Bob	Scala	86
Bob	R	86
Emily	Java	91
Emily	Scala	91
Emily	R	86