В статье предлагается универсальный инструмент для динамического транспонирования таблиц на базе стандартной t-sql функции  PIVOT.

На написание вдохновила другая замечательная статья: T-SQL: Динамическое транспонирование нескольких колонок

 Тем не менее, пытаясь применить  уже имеющие наработки к моей задаче динамического транспонирования колонки  типа datetime в несколько заранее неизвестное число колонок, я столкнулся с ошибкой результата, когда во всех динамических колонках возвращались нулевые значения. Связано это было с тем, что я использовал параметр 120 для  конвертации типа
 datetime в varchar функции CONVERT, тогда как наиболее универсальным решением оказалось значение 126.
Тогда я поставил перед собой задачу найти универсальное  работающее решение развертки в несколько столбцов для столбца  любого типа из следующих:

  • строковые типы varchar, nvarchar, char, nchar
  • числовые типы numeric, float, int, smallint,tinyint,bigint,bit
  • двоичные данные binary, varbinary
  • типы даты datetime, datetime2.

Рассмотрим пример.

Есть таблица STAT_TABLE, в которой хранится количество обработанных записей SIZE за каждый месяц PERIOD  по каждому виду таблиц TABLENAME и способу обработки данныхTYPE.

Имена таблиц tablename, период period и способ  обработки данных type могут быть каждый раз новыми.

Хороший пример, так как в этой таблице имеются столбцы с разными типами данных: varchar, datetime, int.,

Исходная таблица для транспонирования STAT_TABLE выглядит так:

tablename

period

type

size

_Document220

2013-01-01 00:00:00.000

2

16

_Document222

2013-01-01 00:00:00.000

2

17

_Document234

2012-12-01 00:00:00.000

3

19

_Document234

2013-01-01 00:00:00.000

2

59

_Document234

2013-02-01 00:00:00.000

1

66

_Document234

2013-03-01 00:00:00.000

1

66

_Document242

2013-02-01 00:00:00.000

1

16

_Document248

2013-02-01 00:00:00.000

1

20

_Document249

2013-01-01 00:00:00.000

2

18

_Document249

2013-02-01 00:00:00.000

1

19

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

Что мы хотим увидеть на выходе?

Транспонирование по полю period:

type

tablename

01 дек 12

01 янв 13

01 фев 13

01 мар 13

2

_Document220

NULL

16

NULL

NULL

2

_Document222

NULL

17

NULL

NULL

2

_Document234

NULL

59

NULL

NULL

1

_Document234

NULL

NULL

66

66

3

_Document234

19

NULL

NULL

NULL

1

_Document242

NULL

NULL

16

NULL

1

_Document248

NULL

NULL

20

NULL

1

_Document249

NULL

NULL

19

NULL

2

_Document249

NULL

18

NULL

NULL

Транспонирование по полю tablename:

type

period

_Document220

_Document222

_Document234

_Document242

_Document248

_Document249

3

2012-12-01 00:00:00.000

NULL

NULL

19

NULL

NULL

NULL

2

2013-01-01 00:00:00.000

16

17

59

NULL

NULL

18

1

2013-02-01 00:00:00.000

NULL

NULL

66

16

20

19

1

2013-03-01 00:00:00.000

NULL

NULL

66

NULL

NULL

NULL

Транспонирование по полю type:

period

tablename

1

2

3

2012-12-01 00:00:00.000

_Document234

NULL

NULL

19

2013-01-01 00:00:00.000

_Document220

NULL

16

NULL

2013-01-01 00:00:00.000

_Document222

NULL

17

NULL

2013-01-01 00:00:00.000

_Document234

NULL

59

NULL

2013-01-01 00:00:00.000

_Document249

NULL

18

NULL

2013-02-01 00:00:00.000

_Document234

66

NULL

NULL

2013-02-01 00:00:00.000

_Document242

16

NULL

NULL

2013-02-01 00:00:00.000

_Document248

20

NULL

NULL

2013-02-01 00:00:00.000

_Document249

19

NULL

NULL

2013-03-01 00:00:00.000

_Document234

66

NULL

NULL


В ходе решения конкретной задачи родилась универсальная процедура динамического транспонирования  таблиц на базе стандартной функции
 T-SQL PIVOT со следующими параметрами:
1)@sourcename  - имя исходной таблицы для транспонирования;
2)@targetname - имя таблицы, в которую будут сохранены результаты транспонирования. Если таблица уже существует, она будет удалена  и создана с новой структурой.
3)@column2Header - имя колонки, подвергаемой динамическому транспонированию
4)@aggregatedColumn имя колонки, подвергаемой статистической обработке
5) @aggFunction - имя статической функции, выполняемой над  aggregatedColumn.
6) @OrderClause – условие сортировки для выходных данных

Некоторые технические ограничения:

  1. Если в исходной таблице есть поля типа timestamp, они будут исключены из  таблицы-результата.
  2. Таблица-приемник должна быть обычной таблицей базы данных или временной глобальной таблицей ##, но не временной локальной #. Имена временных таблиц требуется передавать с префиксом tempdb.dbo.

Далее текст процедуры динамического транспонирования, создание временной таблицы источника данных, заполнение ее данными из примера, вызов процедуры транспонирования:

GO
DROP PROCEDURE dbo.pivottable
GO
CREATE PROCEDURE pivottable
(@sourcename sysname,
@targetname sysname,
@column2Header sysname,
@aggregatedColumn sysname,
@aggFunction nvarchar(20)='max',
@OrderClause nvarchar(max) = ''
)
AS
BEGIN
    DECLARE
        @IsTempTable bit =0, --признак: 0 - обычная таблица, 1 - временная.
        @Header nvarchar(max) = '', -- список динамических полей для SELECT
        @HeaderList nvarchar(max) = '',-- список динамических полей через запятую.
        @headerdefinition nvarchar(max) = '',--список динамических полей с типами данных для создания новой таблицы.
        @RestColumnList nvarchar(max) = '', -- список полей не подверженных транспонированию.
        @RestColumnDefinition nvarchar(max) = '', -- список полей не подверженных транспонированию для создания новой таблицы.
        @n varchar(1) = '',--разделитель-запятая для перечислений полей.
        @headcol_type varchar(50), --имя типа данных динамического поля.
        @aggCol_type varchar(50), --имя типа данных поля статистической обработки.
        @Col_Prefix sysname = '', -- префикс динамических колонок для числовых и бинарных типов данных.
        @CMD nvarchar(max) --команда для sp_executesql.
 
    -- проверяем временная таблица или постоянная
    Select @IsTempTable = 1
    where @sourcename like 'tempdb.%'
    or @sourcename like '[[]tempdb].%'
    or @sourcename like '#%'
 
    SELECT
        @headcol_type = t.name
    FROM (SELECT
        cc.object_id,
        cc.system_type_id,
        cc.user_type_id,
        cc.name
    FROM sys.columns cc (NOLOCK)
    WHERE cc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 0
    UNION
    SELECT
        tc.object_id,
        tc.system_type_id,
        tc.user_type_id,
        tc.name
    FROM tempdb.sys.columns tc (NOLOCK)
    WHERE tc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 1) AS c
    INNER JOIN sys.systypes t
        ON t.xtype = c.system_type_id
        AND t.xusertype = c.user_type_id
    WHERE c.object_id = OBJECT_ID(@sourcename)
    AND c.name = @column2Header
 
    SELECT
        @aggCol_type =
            CASE
                WHEN t.name IN ('varchar', 'char', 'nvarchar', 'binary', 'varbinary') THEN t.name + '(' + CAST(c.max_length AS nvarchar(10)) + ')'
                WHEN t.name IN ('numeric') THEN t.name + '(' + CAST(c.precision AS nvarchar(10)) + ',' + CAST(c.scale AS nvarchar(10)) + ')'
                ELSE t.name
            END
    FROM (SELECT
        cc.object_id,
        cc.system_type_id,
        cc.user_type_id,
        cc.name,
        cc.max_length,
        cc.precision,
        cc.scale
    FROM sys.columns cc (NOLOCK)
    WHERE cc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 0
    UNION
    SELECT
        tc.object_id,
        tc.system_type_id,
        tc.user_type_id,
        tc.name,
        tc.max_length,
        tc.precision,
        tc.scale
    FROM tempdb.sys.columns tc (NOLOCK)
    WHERE tc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 1) AS c
    INNER JOIN sys.systypes t
        ON t.xtype = c.system_type_id
        AND t.xusertype = c.user_type_id
    WHERE c.object_id = OBJECT_ID(@sourcename)
    AND c.name = @aggregatedColumn
 
    PRINT '@aggCol_type = ' + @aggCol_type
 
    if @headcol_type in ('int','bigint', 'smallint','tinyint', 'numeric','binary','varbinary')
        SET @Col_Prefix = @column2Header+'_'
 
    SET @cmd = N' SELECT @HeaderList = @HeaderList + @n+tt2.HeaderStr,'
    + '@Header = @Header+@n + tt2.header, '
    + '@headerdefinition = @headerdefinition+ @n + tt2.pdefinition,'
    + '@n = '','' '
    + ' from (Select ''['' +CONVERT(sysname,' + @column2Header + ',126)+'']'' HeaderStr,'
    + '''[''+CONVERT(sysname,' + @column2Header + ',126)+''] AS ['' +@Col_Prefix+ Convert(sysname,' + @column2Header + ',6) +'']'' header,'
    + '''[''+@Col_Prefix + Convert(sysname,' + @column2Header + ',6) +''] ' + @aggCol_type + ''' pdefinition '
    + ' from (select distinct ' + @column2Header + ' from ' + @sourcename + ' where ' + @column2Header + ' is not null) as tt) as tt2'
    PRINT @cmd
    EXEC sp_executesql  @CMD,
                        N'@HeaderList NVARCHAR(MAX) output, @Header NVARCHAR(MAX) output, @headerdefinition NVARCHAR(MAX) output, @n varchar(1),@Col_Prefix sysname',
                        @HeaderList OUTPUT,
                        @Header OUTPUT,
                        @headerdefinition OUTPUT,
                        @n,
                        @Col_Prefix
 
 
    PRINT '@HeaderList=' + ISNULL(@HeaderList, 'null')
    PRINT '@Header=' + ISNULL(@Header, 'null')
    PRINT '@headerdefinition=' + ISNULL(@headerdefinition, 'null')
 
    SELECT
        @RestColumnDefinition = @RestColumnDefinition + @n + '[' + c.name + '][' + t.name + ']' +
        CASE
            WHEN t.name IN ('varchar', 'char', 'nvarchar', 'binary', 'varbinary') THEN '(' + CAST(c.max_length AS nvarchar(10)) + ')'
            WHEN t.name IN ('numeric') THEN '(' + CAST(c.precision AS nvarchar(10)) + ',' + CAST(c.scale AS nvarchar(10)) + ')'
            ELSE ''
        END,
        @RestColumnList = @RestColumnList + @n + '[' + c.name + ']',
        @n = ','
    FROM (SELECT
        cc.object_id,
        cc.system_type_id,
        cc.user_type_id,
        cc.name,
        cc.max_length,
        cc.precision,
        cc.scale
    FROM sys.columns cc (NOLOCK)
    WHERE cc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 0
    UNION
    SELECT
        tc.object_id,
        tc.system_type_id,
        tc.user_type_id,
        tc.name,
        tc.max_length,
        tc.precision,
        tc.scale
    FROM tempdb.sys.columns tc (NOLOCK)
    WHERE tc.object_id = OBJECT_ID(@sourcename) and @IsTempTable = 1) AS c
 
    INNER JOIN sys.systypes t
        ON t.xtype = c.system_type_id
        AND t.xusertype = c.user_type_id
 
        AND c.name NOT IN (@column2Header, @aggregatedColumn)
        AND t.name <> 'timestamp'
 
    PRINT '@RestColumnDefinition=' + @RestColumnDefinition
    PRINT '@RestColumnList=' + @RestColumnList
    SET @CMD =
    N'IF OBJECT_ID(''' + @targetname + ''') IS NOT NULL DROP TABLE ' + @targetname + '; '
    + 'CREATE TABLE ' + @targetname + ' (' + @RestColumnDefinition + ',' + @headerdefinition + '); '
    PRINT @cmd
    EXEC sp_executesql @CMD
 
    SET @CMD =
    N' INSERT  INTO  ' + @targetname + ' SELECT ' + @RestColumnList + ',' + @Header
    + '  FROM ' + @sourcename + ' p  '
    + ' PIVOT ' +
    +' ('+@aggFunction+'(' + @aggregatedColumn + ') '
    + ' FOR ' + @column2Header + ' IN (' + @HeaderList + ')) AS pvt '
    print @cmd
    EXEC sp_executesql @CMD
 
    SET @CMD = N' SELECT * FROM ' + @targetname
    IF @OrderClause <> ''
        SET @CMD = @CMD + ' ORDER  BY ' + @OrderClause
    EXEC sp_executesql @CMD
END
 
GO
 
IF OBJECT_ID('tempdb.dbo.##STAT_TABLE') is null
    create table ##STAT_TABLE (tablename nvarchar(50), period datetime, type int, size int)
ELSE
    truncate table ##STAT_TABLE
 
INSERT INTO ##STAT_TABLE VALUES ('_Document220',Convert(datetime,'2013-01-01',126),2,16)
INSERT INTO ##STAT_TABLE VALUES ('_Document222',Convert(datetime,'2013-01-01',126),2,17)
INSERT INTO ##STAT_TABLE VALUES ('_Document234',Convert(datetime,'2012-12-01',126),3,19)
INSERT INTO ##STAT_TABLE VALUES ('_Document234',Convert(datetime,'2013-01-01',126),2,59)
INSERT INTO ##STAT_TABLE VALUES ('_Document234',Convert(datetime,'2013-02-01',126),1,66)
INSERT INTO ##STAT_TABLE VALUES ('_Document234',Convert(datetime,'2013-03-01',126),1,66)
INSERT INTO ##STAT_TABLE VALUES ('_Document242',Convert(datetime,'2013-02-01',126),1,16)
INSERT INTO ##STAT_TABLE VALUES ('_Document248',Convert(datetime,'2013-02-01',126),1,20)
INSERT INTO ##STAT_TABLE VALUES ('_Document249',Convert(datetime,'2013-01-01',126),2,18)
INSERT INTO ##STAT_TABLE VALUES ('_Document249',Convert(datetime,'2013-02-01',126),1,19)
 
EXEC dbo.pivottable 'tempdb.dbo.##STAT_TABLE','tempdb.dbo.##STAT_TABLE_PIVOT','tablename','size','max','period'
EXEC dbo.pivottable 'tempdb.dbo.##STAT_TABLE','tempdb.dbo.##STAT_TABLE_PIVOT','period','size', 'max','tablename'
EXEC dbo.pivottable 'tempdb.dbo.##STAT_TABLE','tempdb.dbo.##STAT_TABLE_PIVOT','type',   'size'

Пишите ваши замечания и найденные ошибки.