SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SkillQuiz (
	Coder VARCHAR(300)
	,SQL int
	,Python int
	);
	
INSERT INTO SkillQuiz (Coder,SQL,Python)
VALUES
    ('Bob', 95, 80),
    ('Emily', 98, 70);
2 rows affected

SELECT *
FROM SkillQuiz


Coder
SQL
Python


Bob
95
80

Emily
98
70


SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
			SQL
			,Python
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC


Coder
Subject
Score


Bob
SQL
95

Bob
Python
80

Emily
SQL
98

Emily
Python
70


SELECT STRING_AGG(QUOTENAME(name), ',') AS Unpivot_List
FROM sys.columns
WHERE [object_id] = OBJECT_ID('SkillQuiz')
	AND name <> 'Coder';


Unpivot_List


[Python],[SQL]


CREATE PROCEDURE dbo.UnPivotColumnsToRowsDynamically
  @PivotTable NVARCHAR(255),
  @KeyColumn NVARCHAR(255),
  @UnPivotColumnName NVARCHAR(255)
AS
BEGIN

DECLARE @UnPivotList NVARCHAR(255) = (
SELECT STRING_AGG(QUOTENAME(name), ',')
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@PivotTable)
	AND name <> @KeyColumn
	);
	
DECLARE @UnPivotSQL NVARCHAR(MAX) = N'
SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR ' + @UnPivotColumnName + ' IN (
	' + @UnPivotList + '
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC
';	

SELECT(@UnPivotSQL) AS UnPivotSQL;
	  
EXEC(@UnPivotSQL);

END
DECLARE @PivotTable NVARCHAR(255) = N'SkillQuiz';
DECLARE @KeyColumn NVARCHAR(255) = N'Coder';
DECLARE @UnPivotColumnName SYSNAME = N'Subject';

EXEC dbo.UnPivotColumnsToRowsDynamically @PivotTable, @KeyColumn, @UnPivotColumnName;


UnPivotSQL



SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
	[Python],[SQL]
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC





Coder
Subject
Score


Bob
SQL
95

Bob
Python
80

Emily
SQL
98

Emily
Python
70


ALTER TABLE SkillQuiz
ADD Java int;
INSERT INTO SkillQuiz (Coder,SQL,Python,Java)
VALUES
    ('Ryan', 95, 90, 70),
    ('Josh', 60, 77, 100); 
2 rows affected

SELECT *
FROM SkillQuiz


Coder
SQL
Python
Java


Bob
95
80
null

Emily
98
70
null

Ryan
95
90
70

Josh
60
77
100


DECLARE @PivotTable NVARCHAR(255) = N'SkillQuiz';
DECLARE @KeyColumn NVARCHAR(255) = N'Coder';
DECLARE @UnPivotColumnName SYSNAME = N'Subject';

EXEC dbo.UnPivotColumnsToRowsDynamically @PivotTable, @KeyColumn, @UnPivotColumnName;


UnPivotSQL



SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
	[Java],[Python],[SQL]
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC





Coder
Subject
Score


Bob
SQL
95

Bob
Python
80

Emily
SQL
98

Emily
Python
70

Josh
Java
100

Josh
Python
77

Josh
SQL
60

Ryan
SQL
95

Ryan
Python
90

Ryan
Java
70
Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Emily	SQL	98
Emily	Python	70
Josh	Java	100
Josh	Python	77
Josh	SQL	60
Ryan	SQL	95
Ryan	Python	90
Ryan	Java	70