Отправляет email-рассылки с помощью сервиса Sendsay

Вопросы и ответы по MS SQL Server

Подписаться Бесплатная новостная рассылка Подписчиков 17.253 RSS
←   Апрель 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


За последние 60 дней 1 выпусков (1 раз в 2 месяца)


Сайт рассылки: http://www.itnews.ru
Открыта: 04-03-2003

Автор
Сергей

Статистика

17.253 подписчиков
-3 за неделю
←   Все выпуски   →

Вопросы и ответы по MS SQL Server


Информационный Канал Subscribe.Ru

Вопросы и ответы по MS SQL Server

Выпуск No. 10 от 2003-04-25
Вопрос : Как "развернуть" таблицу или построить cross-tab отчет?
Ответ:
Иногда необходимо повернуть результат выполнения запроса таким образом, чтобы колонки располагались горизонтально, а строки вертикально. Такие действия называются построением сводной таблицы, cross-tab отчета (отчета с перекрестными ссылками) или поворотом данных. Метод такого поворота таблицы описан в Books Online в разделе "Cross-Tab Reports". Исследуем подробнее этот вопрос.
Создадим таблицу продаж для построения отчетов на ее основе: 
CREATE TABLE Sales

(

SaleID int IDENTITY PRIMARY KEY CLUSTERED,

ClientID int,

Date datetime,

Amount money

)

insert Sales values(1,'20010401', 15.48)

insert Sales values(1,'20020302', 134.01)

insert Sales values(1,'20031003', 2346.03)

insert Sales values(2,'20030203', 754.88)

insert Sales values(3,'20010301', 73.07)

insert Sales values(3,'20030402', 734.46)

insert Sales values(4,'20010301', 1567.10)

insert Sales values(4,'20020404', 6575.70)

insert Sales values(4,'20030307', 6575.77)

insert Sales values(4,'20030309', 6575.37)

insert Sales values(5,'20011201', 1975.73)

insert Sales values(5,'20030306', 178965.63)

insert Sales values(6,'20020103', 16785.34)

insert Sales values(6,'20030304', 1705.44)

GO

Первым заданием будет построить такой запрос, чтобы каждая строка соответствовала отдельному месяцу, каждый столбец - году, а в каждой ячейке, находящейся на пересечении соответствующих строки и столбца, содержалась сумма продаж за указанный месяц заданного года. Следуя рекомендациям Books Online, получается следующий код: 
SELECT

  MONTH(Date) AS SaleMonth,

  SUM(CASE YEAR(Date)

        WHEN 2001 THEN Amount

        ELSE 0

      END) AS [2001],

  SUM(CASE YEAR(Date)

        WHEN 2002 THEN Amount

        ELSE 0

      END) AS [2002],

  SUM(CASE YEAR(Date)

        WHEN 2003 THEN Amount

        ELSE 0

      END) AS [2003]

FROM Sales

GROUP BY MONTH(Date)

ORDER BY MONTH(Date)

GO

Результат выполнения: 
SaleMonth   2001                  2002                  2003

----------- --------------------- --------------------- ---------------------

1           .0000                 16785.3400            .0000

2           .0000                 .0000                 754.8800

3           1640.1700             134.0100              193822.2100

4           15.4800               6575.7000             734.4600

10          .0000                 .0000                 2346.0300

12          1975.7300             .0000                 .0000



(6 row(s) affected)

Задание выполнено успешно (к/ф АССА). Но время идет и через год придется переписывать код и указывать другие значения дат. Что делать? Писать функцию для динамического построения запроса.
Ицик Бен-Ган в статье "Динамические перекрестные запросы" предлагает использовать для построения cross-tab отчетов следующую процедуру: 
CREATE PROCEDURE sp_CrossTab

  @table       AS sysname,-- Таблица для построения crosstab отчета

  @onrows      AS nvarchar(128),-- Значение для группировки по строкам

  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки

  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам

  @sumcol      AS sysname = NULL -- Значение для суммирования

AS

SET NOCOUNT ON

DECLARE

  @sql AS varchar(8000),

  @NEWLINE AS char(1)



SET @NEWLINE = CHAR(10)



-- Шаг 1: начало строки SQL.

SET @sql =

  'SELECT' + @NEWLINE +

  '  ' + @onrows +

  CASE

    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias

    ELSE ''

  END



-- Шаг 2: Хранение ключей во временной таблице.

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)



DECLARE @keyssql AS varchar(1000)

SET @keyssql =

  'INSERT INTO #keys ' +

  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +

  'FROM ' + @table



EXEC (@keyssql)



-- Шаг 3: Средняя часть строки SQL.

DECLARE @key AS nvarchar(100)

SELECT @key = MIN(keyvalue) FROM #keys



WHILE @key IS NOT NULL

BEGIN

  SET @sql = @sql + ','                   + @NEWLINE +

    '  SUM(CASE CAST(' + @oncols +

                     ' AS nvarchar(100))' + @NEWLINE +

    '        WHEN N''' + @key +

           ''' THEN ' + CASE

                          WHEN @sumcol IS NULL THEN '1'

                          ELSE @sumcol

                        END + @NEWLINE +

    '        ELSE 0'                      + @NEWLINE +

    '      END) AS [' + @key+']'



  SELECT @key = MIN(keyvalue) FROM #keys

  WHERE keyvalue > @key

END



-- Шаг 4: Конец строки SQL.

SET @sql = @sql         + @NEWLINE +

  'FROM ' + @table      + @NEWLINE +

  'GROUP BY ' + @onrows + @NEWLINE +

  'ORDER BY ' + @onrows



SET NOCOUNT OFF

PRINT @sql  + @NEWLINE-- для отладки

EXEC (@sql)

GO

Используя эту процедуру, поставленное выше задание выполняется с помощью кода: 
EXEC sp_CrossTab

  @table       = 'Sales',

  @onrows      = 'MONTH(Date)',

  @onrowsalias = 'SaleMonth',

  @oncols      = 'YEAR(Date)',

  @sumcol      = 'Amount'

GO

Результат выполнения будет такой же, как показан выше.
Я позволил себе несколько видоизменить код процедуры, убрав лишние украшения динамического кода и добавив возможность вывода итоговой колонки, а также не только суммирование, но и вычисление максимального, минимального и среднего значения. 
IF OBJECT_ID('sp_CrossTab') IS NOT NULL

  DROP PROCEDURE sp_CrossTab

GO

CREATE PROCEDURE sp_CrossTab

  @table       sysname,-- Таблица для построения crosstab отчета

  @onrows      nvarchar(500),-- Значение для группировки по строкам

  @onrowsalias sysname = NULL,-- Псевдоним для группируемой колонки

  @oncols      nvarchar(500),-- Значение для группировки по колонкам

  @sumcol      sysname = NULL,-- Значение для суммирования

  @kind        tinyint = 0,-- Вид операции 0-SUM, 1-MIN, 2-MAX, 3-AVG

  @total       bit = 0,-- Признак вывода итоговой колонки

  @null        nvarchar(50) = '0'-- Нулевое значение

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF



DECLARE

  @sql varchar(8000),

  @NewLine char(2)



SET @NewLine = '

'



-- Шаг 1: начало строки SQL.

SET @sql =

  'SELECT ' + @onrows +

  CASE

    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias

    ELSE ''

  END



-- Шаг 2: Хранение ключей во временной таблице.

CREATE TABLE #keys(keyvalue nvarchar(300) NOT NULL PRIMARY KEY)



DECLARE @keyssql AS varchar(4000)

SET @keyssql =

  'INSERT INTO #keys ' +

  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +

  'FROM ' + @table



EXEC (@keyssql)



-- Шаг 3: Средняя часть строки SQL.

DECLARE @key AS nvarchar(300)

SELECT @key = MIN(keyvalue) FROM #keys



WHILE @key IS NOT NULL

BEGIN

  SET @sql = @sql + ',' + @NewLine +

    CASE @kind

      WHEN 1 THEN 'MIN'

      WHEN 2 THEN 'MAX'

      WHEN 3 THEN 'AVG'

      ELSE 'SUM'

    END +

    '(CASE CAST(' + @oncols + ' AS nvarchar(100)) WHEN N''' + @key +

    ''' THEN ' +

      CASE

        WHEN @sumcol IS NULL THEN '1'

        ELSE @sumcol

      END + ' ELSE '+@null+' END) AS [' + @key+']'



  SELECT @key = MIN(keyvalue) FROM #keys

  WHERE keyvalue > @key

END



IF @total=1

  SET @sql = @sql + ',' + @NewLine +

    CASE @kind

      WHEN 1 THEN 'MIN'

      WHEN 2 THEN 'MAX'

      WHEN 3 THEN 'AVG'

      ELSE 'SUM'

    END +

    '(' +

    CASE

      WHEN @sumcol IS NULL THEN '1'

      ELSE @sumcol

    END + ') AS ['+

    CASE @kind

      WHEN 1 THEN 'МИНИМУМ'

      WHEN 2 THEN 'МАКСИМУМ'

      WHEN 3 THEN 'СРЕДНЕЕ ЗНАЧЕНИЕ'

      ELSE 'ИТОГО'

    END+']'



-- Шаг 4: Конец строки SQL.

SET @sql = @sql         + @NewLine +

  'FROM ' + @table      + @NewLine +

  'GROUP BY ' + @onrows + @NewLine +

  'ORDER BY ' + @onrows



SET NOCOUNT OFF

--PRINT @sql  + @NewLine -- для отладки

EXEC (@sql)

GO

Для создания cross-tab отчетов на основе выборок можно использовать временную таблицу. Пример использования измененной процедуры на основе предварительной выборки данных: 
select ClientID, Date, Amount into #crosstab from Sales



EXEC sp_CrossTab

  @table       = '#crosstab',

  @onrows      = 'ClientID,MONTH(Date)',

  @onrowsalias = 'SaleMonth',

  @oncols      = 'YEAR(Date)',

  @sumcol      = 'Amount',

  @kind        = 3,

  @total       = 1,

  @null        = 'null'



drop table #crosstab

GO

Результат: 
ClientID SaleMonth   2001      2002       2003        СРЕДНЕЕ ЗНАЧЕНИЕ

-------- ----------- --------- ---------- ----------- ----------------

1        3           NULL      134.0100   NULL        134.0100

1        4           15.4800   NULL       NULL        15.4800

1        10          NULL      NULL       2346.0300   2346.0300

2        2           NULL      NULL       754.8800    754.8800

3        3           73.0700   NULL       NULL        73.0700

3        4           NULL      NULL       734.4600    734.4600

4        3           1567.1000 NULL       6575.5700   4906.0800

4        4           NULL      6575.7000  NULL        6575.7000

5        3           NULL      NULL       178965.6300 178965.6300

5        12          1975.7300 NULL       NULL        1975.7300

6        1           NULL      16785.3400 NULL        16785.3400

6        3           NULL      NULL       1705.4400   1705.4400



(12 row(s) affected)

Обратите внимание, что параметр @onrows может содержать список колонок для группировки, а параметры @oncols и @sumcol могут содержать сложные выражения с вычислениями.

Вот вроде бы и все. Осталось только "развернуть" таблицу на 90 градусов: 
EXEC sp_CrossTab

  @table       = 'Sales',

  @onrows      = 'LEFT(STR(SaleID),1)',

  @onrowsalias      = 'i',

  @oncols      = 'STR(SaleID,3)+STR(ClientID,3)+'' ''+CONVERT(char(10),Date,104)',

  @sumcol      = 'Amount'

GO

На этом закончим. Удалить созданные объекты можно с помощью следующего кода: 
DROP TABLE Sales

DROP PROCEDURE sp_CrossTab

GO
АНОНС РАССЫЛОК:
Рассылки сайта progs.biz через Subscribe.Ru
Delphi. Уроки
Уроки по Visual C++ (MFC, платформа .NET)
Уроки по Delphi. Уровень - от начинающего до профи. Каждый урок рассчитан на 15 - 20 минут. Темы - библиотека VCL, создание компонентов, создание ActiveX, COM, COM+, DCOM, FAQ, работа с базами данных.
Уроки по Visual C++.NET. Создание программ для платформ Win32 и .NET. Библиотеки классов .NET, MFC и ATL. Примеры, FAQ. Каждый урок содержит информацию только по существу и расчитан на 10-20 минут. Уроки по каждой теме независимы.
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru
С уважением, Сергей Кошкин.
Адрес сайта Рассылки - http://sql.softmatics.ru/

Архив на Subscribe.Ru
Поиск по архиву рассылки
"Вопросы и ответы по MS SQL Server"

Рейтинг@Mail.ru
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное