SQL Multiple Value Dynamic pivot

Question

Below I have my current code.

What I am trying to do in this code:

The #BySite table has a list of records where all the columns listed before the PlayMonth column are a snap shot at the end of every month in the since 10/1/2021. The columns for PlayMonth down to Actual are the values for each month since 10/1/2021. So for example if the player has a record for Jan 2024 thru Mar 2024 there would be 3 Jan 2024 records one with the play data from the months Jan, Feb and Mar. There would be 2 Feb 2024 records one with play data from Feb and Mar. And there would be 1 record for Mar currently with only the play data from Mar. When a new month passes each of those months will get a new record for Apr. Instead of adding new rows. I want to pivot the rows to a dynamic set of columns so that this player would have 3 row one for Jan, Feb and Mar. The columns would expand out and be Jan_Theo, Jan_Acutal, Feb_Theo, Feb_Actual, Mar_Theo, Mar_Actual. and when Apr is done Apr_Theo, Apr_Actual. The values would be null for the months that are before that rows month. Also I was only testing two values with the pivot but I will be adding several more. ClubLevel, Property, Trips, Theo, Actual, FPare the full list of columns that need to be pivoted.

What I am trying to do as a whole in case there is a better way to approach this:

The goal is to be able to compare what any player did from one month to another in a report where the end user can select any two months and compare them.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @Query AS NVARCHAR(MAX)

SELECT @cols = STUFF((
    SELECT ',' + QUOTENAME(PlayMonth)
    FROM (
        SELECT DISTINCT CAST(BS.Playyear AS VARCHAR(20)) + '|' + CAST(BS.PlayMonth AS VARCHAR(20)) AS PlayMonth
        FROM #BySite AS BS
    ) AS PivotColumns
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @Query = '
SELECT *
FROM (
    SELECT BS.EndingClubLevel
        , BS.PropertyName
        , BS.Year
        , BS.Month
        , BS.Universal_ID
        , BS.Old_Trips
        , BS.Old_Theo
        , BS.Old_Actual
        , BS.Old_FP
        , CAST(BS.Playyear AS VARCHAR(20)) + ''|'' + CAST(BS.PlayMonth AS VARCHAR(20)) AS PlayMonth
        , BS.CurrentClubLevel as ClubLevel
        , BS.PlayProperty as Property
        , BS.Trips
        , BS.theo
        , BS.Actual
        ,BS.FP
    FROM #BySite AS BS
) AS Src

PIVOT (
    AVG(theo) FOR PlayMonth IN (' + @cols + ')
) AS TheoPivot
PIVOT (
    AVG(Actual) FOR PlayMonth IN (' + @cols + ')
) AS ActualPivot';

EXEC sp_executesql @Query;

Edit: Sample data was requested along with desired output and that makes sense so below is that. I have color coded it so I can try to help explain. There are records for two players one in light orange and one in grey. The green headers are the static headers that are snapshots for a given month. The purple headers contain the values for every month and are the ones I wanted to dynamic pivot. This will make it the results section very wide once working and always growing wider by 6 cols per month.

Edit2:

If I run the code with ClubLevel, Property, Trips, Actual and FP all commented out and have the second pivot commented out it works as expected/desired. When I add back the actual col and the pivot for actual I get the below error. it continues for each month year combination.

I am trying to create something along the lines of what I see for the min, max example from this website SQL Pivot

I understand that in there example they just did one pivot with multiple values but I have not gotten that to run without errors either.

Msg 207, Level 16, State 1, Line 129
Invalid column name 'PlayMonth'.
Msg 265, Level 16, State 1, Line 129
The column name "2021|11" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 129
The column name "2021|12" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

Answer 1

I was able to figure out an answer. I had to break out each col into its own variable and use that in the pivots so that they all made unique column names.

DECLARE @Theo AS NVARCHAR(MAX)
DECLARE @Actual AS NVARCHAR(MAX)
DECLARE @Query AS NVARCHAR(MAX)

SELECT @Theo = STUFF((
    SELECT ',' + QUOTENAME(TheoBreakout)
    FROM (
        SELECT DISTINCT CAST(BS.Playyear AS VARCHAR(20)) + '|' + CAST(BS.PlayMonth AS VARCHAR(20))+ '|Theo' AS TheoBreakout
        FROM #BySite AS BS
    ) AS PivotColumns
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @Actual = STUFF((
    SELECT ',' + QUOTENAME(ActualBreakout)
    FROM (
        SELECT DISTINCT CAST(BS.Playyear AS VARCHAR(20)) + '|' + CAST(BS.PlayMonth AS VARCHAR(20)) + '|Actual' AS ActualBreakout
        FROM #BySite AS BS
    ) AS PivotColumns
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @Query = '
SELECT *
FROM (
    SELECT BS.EndingClubLevel
        , BS.PropertyName
        , BS.Year
        , BS.Month
        , BS.Universal_ID
        , BS.Old_Trips
        , BS.Old_Theo
        , BS.Old_Actual
        , BS.Old_FP
        , CAST(BS.Playyear AS VARCHAR(20)) + ''|'' + CAST(BS.PlayMonth AS VARCHAR(20))+ ''|Theo'' AS TheoBreakout
        , CAST(BS.Playyear AS VARCHAR(20)) + ''|'' + CAST(BS.PlayMonth AS VARCHAR(20)) + ''|Actual'' AS ActualBreakout
        --, BS.CurrentClubLevel
        
      --  , BS.PlayProperty
        , BS.theo
       , BS.Actual
    FROM #BySite AS BS
) AS Src

PIVOT (
    AVG(theo) FOR TheoBreakout IN (' + @Theo + ')
) AS TheoPivot
PIVOT (
    AVG(Actual) FOR ActualBreakout IN (' + @Actual + ')
) AS ActualPivot'

EXEC sp_executesql @Query;