СУММА динамических столбцов в таблице PIVOT в SQL Server

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

Моя таблица: ATTENDANCE_MASTER Содержит: ID, Stud_id, ATT_DATE, PRESENT

который хранит такие данные, как:

ID  Stud_id ATT_DATE   PRESENT
1     1     2015-08-1    1
2     2     2015-08-1    0
3     3     2015-08-1    1
4     1     2015-08-2    0
5     2     2015-08-2    1
6     3     2015-08-2    1

Я создал PIVOT-запрос

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ATT_DATE)
  FROM (SELECT p.ATT_DATE FROM dbo.ATTENDANCE_MASTER AS p
  GROUP BY p.ATT_DATE) AS x;

SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM dbo.ATTENDANCE_MASTER AS p
) AS j
PIVOT
(
  SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Мне нужна сумма столбцов, например

Stud_ID  2015-08-01   2015-08-2 2015-08-3 Total
1            1            0         1      2
2            1            1         1      3
3            1            1         0      2
4            0            0         1      1

Пожалуйста, предложите мне решение.

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


sql-server powerpivot pivot pivotviewer activepivot
person QuaBizIT    schedule 07.09.2015    source источник
comment
Я знаю, как сделать сумму статических столбцов, но я не знаю, как суммировать динамические столбцы   -  person QuaBizIT    schedule 07.09.2015
comment
Какой выход у вас есть на данный момент?   -  person Stanislovas Kalašnikovas    schedule 07.09.2015
comment
Что здесь динамичного? количество столбцов даты?   -  person    schedule 07.09.2015
comment
Теперь у меня есть выход выше, кроме Total Column   -  person QuaBizIT    schedule 07.09.2015
comment
Да, имена столбцов даты являются динамическими @ bmsqldev   -  person QuaBizIT    schedule 07.09.2015

Ответы (2)


arrow_upward
2
arrow_downward

Во-первых, я бы рекомендовал не использовать конкатенацию переменных для создания списка столбцов. Его поведение не определено и может быть неожиданным. Вместо этого используйте расширения XML SQL Server:

SET @Columns = (SELECT  N', p.' + QUOTENAME(p.Att_Date)
                FROM    dbo.ATTENDANCE_MASTER AS p
                GROUP BY p.ATT_DATE
                ORDER BY p.ATT_DATE
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');

Затем вы можете просто использовать @Columns для создания общего выражения, используя:

', Total = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '')

Вы получаете что-то вроде:

, Total = p.[2015-08-01] + p.[2015-08-02]

Который вы можете добавить в свой динамический SQL, поэтому для полного рабочего примера:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T ([ID] int, [Stud_id] int, [ATT_DATE] datetime, [PRESENT] int);

INSERT INTO #T ([ID], [Stud_id], [ATT_DATE], [PRESENT])
VALUES
    (1, 1, '2015-08-01 00:00:00', 1),
    (2, 2, '2015-08-01 00:00:00', 0),
    (3, 3, '2015-08-01 00:00:00', 1),
    (4, 1, '2015-08-02 00:00:00', 0),
    (5, 2, '2015-08-02 00:00:00', 1),
    (6, 3, '2015-08-02 00:00:00', 1);

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);             

SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.Att_Date, 111), '/', '-'))
                FROM    #T AS p
                GROUP BY p.ATT_DATE
                ORDER BY p.ATT_DATE
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');

SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + ', Total = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + '
FROM
(
  SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM #T AS p
) AS j
PIVOT
(
  SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
person GarethD    schedule 07.09.2015
comment
У меня есть еще один запрос: как я могу использовать в нем две агрегатные функции, такие как Sum и Count. - person QuaBizIT; 09.09.2015

arrow_upward
1
arrow_downward

Вы можете добавить GROUP BY WITH CUBE, чтобы получить итог следующим образом:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ATT_DATE)
  FROM (SELECT p.ATT_DATE FROM dbo.ATTENDANCE_MASTER AS p
  GROUP BY p.ATT_DATE) AS x;

SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.ATT_DATE, p.Stud_id, p.PRESENT 
  FROM dbo.ATTENDANCE_MASTER AS p
  GROUP BY p.ATT_DATE, p.Stud_id, p.PRESENT 
  WITH CUBE
) AS j
PIVOT
(
  SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
person Stanislovas Kalašnikovas    schedule 07.09.2015