combine 2 columns to one and pivot

Question

How do I combine 2 columns to 1 and pivot that table.

CREATE TABLE #Test 
(
	StudentName VARCHAR(20)
	,Class1Present INT
	,Class1Absent int
	,Class2Present INT
	,Class2Absent int
	,Class3Present INT
	,Class3Absent int

)

INSERT INTO #Test VALUES ('John', 0,0,3,2,3,4)
INSERT INTO #Test VALUES ('Mary', 5,1,4,3,4,4)
INSERT INTO #Test VALUES ('Tom', 0,6,7,0,3,3)
INSERT INTO #Test VALUES ('Susan', 1,5,3,4,4,3)

SELECT * FROM #Test

Suppose my table looks like this

and I want it to look like this

Answer 1

Hi @ojm

Please check this query:

;WITH CTE1 AS
(
 SELECT StudentName
       ,CONCAT(Class1Present,'/',Class1Absent) AS Class1
	   ,CONCAT(Class2Present,'/',Class2Absent) AS Class2
	   ,CONCAT(Class3Present,'/',Class3Absent) AS Class3
 FROM #Test
),CTE2 AS
(
 SELECT * FROM CTE1 
 UNPIVOT(Present_Absent FOR Class IN([Class1],[Class2],[Class3]))U
)
SELECT Class AS StudentName,[John],[Mary],[Tom],[Susan]
FROM CTE2
PIVOT(MAX(Present_Absent)FOR StudentName IN ([John],[Mary],[Tom],[Susan]))P

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Olá, a forma mais rápida é criar uma tabela virtual para cada CLASS, ou seja, 3 tabelas e por fim relacioná-las pelos estudantes. Mas é importante que todos os estudantes possuam valores em todos os campos, do contrário, seria preciso utilizar um LEFT e/ou RIGTH JOIN ou tratar os campos nulos com um NVL() talvez. Acredito que essa seria a forma mais simples e rápida, mas daria para utilizar algumas funções de PL/SQL para fazer isso de forma automática.