Блог

У меня есть данные метрики таблицы

 Metric ID         Metric Date             Metric Result       Metric Calculation Color
--------------------------------------------------------------------------------------
001              2004-04-01                 1                   GREEN
002              2004-04-01                 0                   RED
001              2004-05-01                 1                   GREEN
002              2004-05-01                 5                   YELLOW
003              2004-05-01                 2                   GREEN
  

Я хочу, чтобы результат задавал что-то вроде

 Metric ID        April Result           April Calc Color    May Result   May Calc Color
---------------------------------------------------------------------------------------
001               1                       GREEN                1          RED
002               0                       RED                  5          YELLOW
003                                                            2          GREEN
  

Я использовал концепцию сводной таблицы, но она дает мне повторяющиеся результаты, такие как

 Metric ID        April Result           April Calc Color    May Result   May Calc Color
---------------------------------------------------------------------------------------
001               1                       GREEN                NULL       NULL
001               NULL                    NULL                 1          RED
002               0                       RED                  NULL       NULL
002               NULL                    NULL                 5          YELLOW
003                                                            2          GREEN
  

Это запрос, который я использовал

 ;With CTE AS
( 
SELECT * FROM ( SELECT C.METRIC_ID,RESULT,COLOR FROM METRICDATA ) Q PIVOT (MAX(RESULT) FOR [RESULT] IN ([April],[May]) )Pv ) PIVOT (MAX(COLOR) FOR [RESULT] IN ([April],[May]) )Pv1 )
  

Результат показателя и столбец цвета вычисления должны быть сгенерированы на основе ограничения даты, предоставленного запросом.

Заранее спасибо.

Попробуйте это вместо pivot

 Select metricId,
Case when datepart(Metric_Date)=4 then Metric_Result else o end 'April Result',
Case when datepart(Metric_Date)=4 then Metric_Color else NULL end 'April Calc Color',
Case when datepart(Metric_Date)=5 then Metric_Result else o end 'MayResult',
Case when datepart(Metric_Date)=5 then Metric_Color else NULL end 'May Calc Color'
from Metric
  

Это было весело. SQLFiddle не нравится мой код (похоже, мне не очень нравятся переменные), но это работает в моем локальном окне. Вот схема для тестирования:

 CREATE TABLE METRICDATA
    ([Metric_ID] varchar(3), [Date] date, [Result] int, [Color] varchar(6))
;

INSERT INTO METRICDATA
    ([Metric_ID], [Date], [Result], [Color])
VALUES
    ('001', '2004-04-01', 1, 'GREEN'),
    ('002', '2004-04-01', 0, 'RED'),
    ('001', '2004-05-01', 1, 'GREEN'),
    ('002', '2004-05-01', 5, 'YELLOW'),
    ('003', '2004-05-01', 2, 'GREEN')
;
  

И вот код:

 DECLARE @StartDate AS DATE,
        @EndDate AS DATE,
        @SQL VARCHAR(MAX);

SET @StartDate = '20040401';
SET @EndDate = '20040501';

WITH cteWithMonths
AS
(
    SELECT      Metric_ID,
                DATENAME(MONTH, Date) AS MonthName,
                DATEPART(M, Date) AS Month,
                MAX(DATEPART(M, Date)) OVER () AS MaxMonth,
                Color
    FROM
                METRICDATA
    WHERE
                Date BETWEEN @StartDate AND @EndDate
    GROUP BY
                Metric_ID,
                DATENAME(MONTH, Date),
                DATEPART(M, Date),
                Color
),
cteSQLFieldList
AS
(
    SELECT      0 AS LineNum, 'SELECT DISTINCT m.Metric_ID,' AS SQL
    UNION ALL
    SELECT      DISTINCT
                MONTH,
                CASE 
                    WHEN Month < MaxMonth THEN MonthName   '.'   MonthName   '_Result, '   MonthName   '.'   MonthName   '_Calc_Color,'
                    ELSE MonthName   '.'   MonthName   '_Result, '   MonthName   '.'   MonthName   '_Calc_Color'
                END
    FROM        cteWithMonths
    UNION ALL
    SELECT      13,
                'FROM METRICDATA m'
),
cteSQLJoinList
AS
(
    SELECT      DISTINCT
                MONTH   13 AS LineNum,
                'LEFT JOIN (SELECT Metric_ID, SUM(RESULT) AS '   MonthName   '_Result, Color AS '   MonthName   '_Calc_Color FROM METRICDATA WHERE DATENAME(MONTH, Date) = '''   MonthName   ''' GROUP BY Metric_ID, Color) '   MonthName  ' ON m.Metric_ID = '   MonthName   '.Metric_ID' AS SQL
    FROM        cteWithMonths
),
cteSQLOrderByList
AS
(
    SELECT      26 AS LineNum,
                'ORDER BY m.Metric_ID' AS SQL
),
cteSQL_ALL
AS
(
    SELECT      *
    FROM        cteSQLFieldList
    UNION
    SELECT      *
    FROM        cteSQLJoinList
    UNION
    SELECT      *
    FROM        cteSQLOrderByList
)

SELECT      @SQL = COALESCE(@SQL   ' ', '')   SQL
FROM        cteSQL_ALL
ORDER BY    LineNum;

EXEC(@SQL);
  

Честно говоря, я не хотел играть с вашим сводным решением. Динамическое построение выглядело сложным. Вместо этого я выбрал агрегирование для каждого Metric_ID в выделенных таблицах, а затем ОБЪЕДИНИЛ их все вместе. Я создаю необходимый динамический SQL, а затем выполняю его.