SQL Server 2022

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

CREATE TABLE ##MHSDS_204_Indirect_Activity_DeDuplicated  
(
    [Source]	VARCHAR(512),
    ReportingPeriodStartDate	VARCHAR(512),
    ReportingPeriodStartDate_YYYYMM	VARCHAR(512),
    Healthcare_Provider	VARCHAR(512),
    TheRowTotal	INT,
    [%ServiceRequestId]	INT,
    [%IndirectActDate] INT
);

INSERT INTO ##MHSDS_204_Indirect_Activity_DeDuplicated  ([Source], ReportingPeriodStartDate, ReportingPeriodStartDate_YYYYMM, Healthcare_Provider, TheRowTotal, [%ServiceRequestId], [%IndirectActDate] ) VALUES
	(1, '2024-1-1', '202401', 'RAMQ', 100, 10, 10),
	(1, '2024-1-2', '202402', 'OHIP', 90, 11, 11),
	(1, '2024-1-3', '202403', 'BCHC', 80, 12, 12),
	(1, '2024-1-4', '202404', 'MHSPI', 70, 13, 13),
	(1, '2024-1-5', '202405', 'AHCIP', 60, 14, 14),
	(1, '2024-1-6', '202406', 'SMH', 50, 15, 15);

SELECT * FROM ##MHSDS_204_Indirect_Activity_DeDuplicated

select [Source], ReportingPeriodStartDate, ReportingPeriodStartDate_YYYYMM, Healthcare_Provider,
           u.pct, u. [Values]
from ##MHSDS_204_Indirect_Activity_DeDuplicated  s
unpivot
(
  [Values]
  for pct in (TheRowTotal, [%ServiceRequestId], [%IndirectActDate])
) u;

Source	ReportingPeriodStartDate	ReportingPeriodStartDate_YYYYMM	Healthcare_Provider	TheRowTotal	%ServiceRequestId	%IndirectActDate
1	2024-1-1	202401	RAMQ	100	10	10
1	2024-1-2	202402	OHIP	90	11	11
1	2024-1-3	202403	BCHC	80	12	12
1	2024-1-4	202404	MHSPI	70	13	13
1	2024-1-5	202405	AHCIP	60	14	14
1	2024-1-6	202406	SMH	50	15	15

Source	ReportingPeriodStartDate	ReportingPeriodStartDate_YYYYMM	Healthcare_Provider	pct	Values
1	2024-1-1	202401	RAMQ	TheRowTotal	100
1	2024-1-1	202401	RAMQ	%ServiceRequestId	10
1	2024-1-1	202401	RAMQ	%IndirectActDate	10
1	2024-1-2	202402	OHIP	TheRowTotal	90
1	2024-1-2	202402	OHIP	%ServiceRequestId	11
1	2024-1-2	202402	OHIP	%IndirectActDate	11
1	2024-1-3	202403	BCHC	TheRowTotal	80
1	2024-1-3	202403	BCHC	%ServiceRequestId	12
1	2024-1-3	202403	BCHC	%IndirectActDate	12
1	2024-1-4	202404	MHSPI	TheRowTotal	70
1	2024-1-4	202404	MHSPI	%ServiceRequestId	13
1	2024-1-4	202404	MHSPI	%IndirectActDate	13
1	2024-1-5	202405	AHCIP	TheRowTotal	60
1	2024-1-5	202405	AHCIP	%ServiceRequestId	14
1	2024-1-5	202405	AHCIP	%IndirectActDate	14
1	2024-1-6	202406	SMH	TheRowTotal	50
1	2024-1-6	202406	SMH	%ServiceRequestId	15
1	2024-1-6	202406	SMH	%IndirectActDate	15