Create pivot unpivot in same table

1

Dear all, I have a table as follows.

ID Character Jan Feb Mar
1 X 2 5 8
1 Y 5 2 9
1 z 23 45 43

I want to pivot and unpivot in such a way that the data looks like follows

ID Month X Y Z
1 Jan 2 5 23
1 Feb 5 2 45
1 Mar 8 9 43

Please help me with the query

Thanks

2

Maybe something like:

WITH UnPivotResults
AS
(
	SELECT ID, [Character], [Month], Total
	FROM
	(
		SELECT ID, [Character], Jan, Feb, Mar
		FROM YourTable
	) P
	UNPIVOT
	(
		Total FOR [Month] IN (Jan, Feb, Mar)
	) U
)
SELECT ID, [Month], X, Y, Z
FROM
(
	SELECT ID, [Character], [Month], Total
	FROM UnPivotResults
) S
PIVOT
(
	AVG(Total)  
	FOR [Character] IN (X, Y, Z)  	
) P;