WIKI de apuntes de Billy Rondón

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== SQL SERVER PIVOT ======

excelente explicacion:

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

<code sql>
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
</code>
[[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]]



===== NOTAS =====

<code sql>
-- 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)

</code>

==== Pivot incompatibilidad ====

En una DB me salio:
<code>
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.
</code>

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

Para ver la compatibilidad de la DB
<code sql>
SELECT @@version;
SELECT compatibility_level FROM sys.databases WHERE name = 'db_name';
</code>

<code sql>
ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 100
-- (mant estaba en 80 antes)
</code>

  * [[https://stackoverflow.com/questions/21483372/incorrect-syntax-near-pivot]]

===== PIVOT con Columnas dinamicas ======

<code sql>

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);

</code>

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