excelente explicacion:

https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/

WITH Src AS
(
    SELECT * FROM (VALUES   
    ('1/10/2016', 'abc11', 'teamA', 'ID_1 ', 292, 3),
    ('1/11/2016', 'abc11', 'teamA', 'ID_1 ', 300, 0),
    ('1/10/2016', 'abc11', 'teamA', 'ID_10', 100, 0),
    ('1/11/2016', 'abc11', 'teamA', 'ID_10', 84, 0),
    ('1/10/2016', 'abc11', 'teamA', 'ID_11', 11, 0),
    ('1/11/2016', 'abc11', 'teamA', 'ID_11', 12, 0)
    )T(MTH, ID, TEAM, Metric_ID, Score, Outcome_Score)
)
SELECT * FROM
(
    SELECT ID, TEAM, Metric_ID, VALUE,
        CASE WHEN DATEPART(M, MTH)=10 THEN 'Oct_'
             WHEN DATEPART(M, MTH)=11 THEN 'Nov_' END+Col ToPivot
    FROM
    (SELECT CONVERT(DATE, MTH, 103) MTH, ID, TEAM, Metric_ID, Score, Outcome_Score FROM Src) T1
    UNPIVOT (VALUE FOR Col IN (Score, Outcome_Score)) U1
) T
PIVOT (SUM(VALUE) FOR ToPivot IN (Oct_Score, Oct_Outcome_Score, Nov_Score, Nov_Outcome_Score)) P

https://stackoverflow.com/questions/39739230/tsql-pivot-multiple-columns

https://stackoverflow.com/questions/31855778/pivot-on-multiple-columns-t-sql

Multiple Pivots encadenado

https://stackoverflow.com/questions/38067490/in-sql-server-how-to-pivot-for-multiple-columns

https://dba.stackexchange.com/questions/192524/how-to-pivot-on-multiple-columns-in-sql-server

-- INTERESANTE, TABLA SIN DECLARARLA
 SELECT * FROM (VALUES   
    ('1/10/2016', 'abc11', 'teamA', 'ID_1 ', 292, 3),
    ('1/11/2016', 'abc11', 'teamA', 'ID_1 ', 300, 0),
    ('1/10/2016', 'abc11', 'teamA', 'ID_10', 100, 0),
    ('1/11/2016', 'abc11', 'teamA', 'ID_10', 84, 0),
    ('1/10/2016', 'abc11', 'teamA', 'ID_11', 11, 0),
    ('1/11/2016', 'abc11', 'teamA', 'ID_11', 12, 0)
)T(MTH, ID, TEAM, Metric_ID, Score, Outcome_Score)

En una DB me salio:

Msg 325, Level 15, State 1, Line 31
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

• https://stackoverflow.com/questions/25860469/is-pivot-supported-with-compatibility-level-80

Para ver la compatibilidad de la DB

SELECT @@version;
SELECT compatibility_level FROM sys.databases WHERE name = 'db_name';

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 100
-- (mant estaba en 80 antes)

• https://stackoverflow.com/questions/21483372/incorrect-syntax-near-pivot

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));
 
INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(VARCHAR(48), [ITEM_HEAD_ID]) + ']'
FROM   [TABLE];
 
DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);
 
SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR(50), [ColumnName])
                                   FROM   @ColumnsTable
                                   FOR XML PATH('')), 1, 2, ''));
SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(VARCHAR(50), [ColumnName]) + ', 0)'
                                  FROM   @ColumnsTable
                                  FOR XML PATH('')), 1, 3, ''));
SET @SQL = 'SELECT *, (' + @TotalColumn + ') AS [Total]
FROM   (SELECT [TRXID],
               [ITEM_HEAD_ID],
               [ITEM_HEAD_AMT]
        FROM   [Table]) AS t
       PIVOT (MAX([ITEM_HEAD_AMT])
             FOR [ITEM_HEAD_ID] IN (' + @PivotColumns + ')) AS p;';
 
EXEC(@SQL);

• https://www.codeproject.com/Questions/761828/How-to-create-a-pivot-query-in-sql-server-without