问如何在多个(我的案例3)值上应用透视？

EN

/*Setup Data */
DROP TABLE IF EXISTS tmptable
GO
SELECT
    * INTO tmptable
FROM (VALUES(2016, 1150, NULL, 'CALCULATED', 'IN')
, (2017, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
, (2018, 1150, 'Zum Verkauf', 'MANUAL', 'IN')
, (2019, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
) a (YEAR, ID, COMMENT, STATUS, "IN/OUT")


GO

/*Start Solution */
DROP TABLE IF EXISTS #tmpyear
GO
/* Select which columns are immutable, in you case ID */
DECLARE @IDCols VARCHAR(100) = 'ID'
/* Select which columns to Pivot, as a list*/
DECLARE @PivotCols VARCHAR(100) = 'COMMENT,STATUS,IN/OUT'
DECLARE @SQL NVARCHAR(MAX)

/* Find out how many years are in the data */
SELECT DISTINCT
    CAST(year AS VARCHAR(100)) [year] INTO #tmpyear
FROM tmptable t

/* Build the query */
SET @SQL = N'SELECT ' + @IDCols + ', '
SELECT
@SQL=@SQL+STRING_AGG(CAST( 'max(iif( [year]='+t.[year]+',['+c.[value]+'],''''))'
+'['+ c.[value]+' for '+ T.[year]+']' AS NVARCHAR(MAX)),',')
WITHIN GROUP (ORDER BY c.[value],T.[year])
FROM #tmpyear t
CROSS JOIN STRING_SPLIT(@PivotCols,',') c

SET @SQL = @SQL + 'FROM tmptable group by ' + @IDCols
EXEC (@SQL)

SELECT
    id
   ,MAX(IIF([year] = 2016, [COMMENT], '')) [COMMENT for 2016]
   ,MAX(IIF([year] = 2017, [COMMENT], '')) [COMMENT for 2017]
   ,MAX(IIF([year] = 2018, [COMMENT], '')) [COMMENT for 2018]
   ,MAX(IIF([year] = 2019, [COMMENT], '')) [COMMENT for 2019]
   ,MAX(IIF([year] = 2016, [IN/OUT], '')) [IN/OUT for 2016]
   ,MAX(IIF([year] = 2017, [IN/OUT], '')) [IN/OUT for 2017]
   ,MAX(IIF([year] = 2018, [IN/OUT], '')) [IN/OUT for 2018]
   ,MAX(IIF([year] = 2019, [IN/OUT], '')) [IN/OUT for 2019]
   ,MAX(IIF([year] = 2016, [STATUS], '')) [STATUS for 2016]
   ,MAX(IIF([year] = 2017, [STATUS], '')) [STATUS for 2017]
   ,MAX(IIF([year] = 2018, [STATUS], '')) [STATUS for 2018]
   ,MAX(IIF([year] = 2019, [STATUS], '')) [STATUS for 2019]
FROM tmptable
GROUP BY id

SELECT
    * INTO tmptable
FROM (VALUES(2016, 1150, NULL, 'CALCULATED', 'IN')
, (2017, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
, (2018, 1150, 'Zum Verkauf', 'MANUAL', 'IN')
, (2019, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
, (2020, 1150, 'Zum Verkauf', 'MANUAL', 'IN')
, (2021, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
, (2022, 1150, NULL, 'MANUAL', 'IN')
, (2023, 1150, 'Zum Verkauf', 'MANUAL', 'OUT')
) a (YEAR, ID, COMMENT, STATUS, "IN/OUT")

SELECT
    id
   ,MAX(IIF([year] = 2016, [COMMENT], '')) [COMMENT for 2016]
   ,MAX(IIF([year] = 2017, [COMMENT], '')) [COMMENT for 2017]
   ,MAX(IIF([year] = 2018, [COMMENT], '')) [COMMENT for 2018]
   ,MAX(IIF([year] = 2019, [COMMENT], '')) [COMMENT for 2019]
   ,MAX(IIF([year] = 2020, [COMMENT], '')) [COMMENT for 2020]
   ,MAX(IIF([year] = 2021, [COMMENT], '')) [COMMENT for 2021]
   ,MAX(IIF([year] = 2022, [COMMENT], '')) [COMMENT for 2022]
   ,MAX(IIF([year] = 2023, [COMMENT], '')) [COMMENT for 2023]
   ,MAX(IIF([year] = 2016, [IN/OUT], '')) [IN/OUT for 2016]
   ,MAX(IIF([year] = 2017, [IN/OUT], '')) [IN/OUT for 2017]
   ,MAX(IIF([year] = 2018, [IN/OUT], '')) [IN/OUT for 2018]
   ,MAX(IIF([year] = 2019, [IN/OUT], '')) [IN/OUT for 2019]
   ,MAX(IIF([year] = 2020, [IN/OUT], '')) [IN/OUT for 2020]
   ,MAX(IIF([year] = 2021, [IN/OUT], '')) [IN/OUT for 2021]
   ,MAX(IIF([year] = 2022, [IN/OUT], '')) [IN/OUT for 2022]
   ,MAX(IIF([year] = 2023, [IN/OUT], '')) [IN/OUT for 2023]
   ,MAX(IIF([year] = 2016, [STATUS], '')) [STATUS for 2016]
   ,MAX(IIF([year] = 2017, [STATUS], '')) [STATUS for 2017]
   ,MAX(IIF([year] = 2018, [STATUS], '')) [STATUS for 2018]
   ,MAX(IIF([year] = 2019, [STATUS], '')) [STATUS for 2019]
   ,MAX(IIF([year] = 2020, [STATUS], '')) [STATUS for 2020]
   ,MAX(IIF([year] = 2021, [STATUS], '')) [STATUS for 2021]
   ,MAX(IIF([year] = 2022, [STATUS], '')) [STATUS for 2022]
   ,MAX(IIF([year] = 2023, [STATUS], '')) [STATUS for 2023]
FROM tmptable
GROUP BY id