Транспонирование последовательности целых чисел в таблицу. Транспонировать таблицу ms sql

Универсальное динамическое транспонирование таблиц T-SQL (Dinamic PIVOT) - Статьи TechNet - Россия (Pусский)

В статье предлагается универсальный инструмент для динамического транспонирования таблиц на базе стандартной 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 – условие сортировки для выходных данных

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

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

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

DROP PROCEDURE dbo.pivottable

CREATE PROCEDURE pivottable

(@sourcename sysname,

@targetname sysname,

@column2Header sysname,

@aggregatedColumn sysname,

@aggFunction nvarchar(20)='max',

@OrderClause nvarchar(max) = ''

)

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,

social.technet.microsoft.com

sql - Транспонирование/поворот строк в столбцы в SQL Server

У меня есть три таблицы - первый содержит вопросы с уникальным идентификатором

Второй содержит ответы на эти вопросы, уникальный идентификатор из первой таблицы, а затем идентификатор записи, которая связывает ответы вместе

QID | Answer | Parent ------------------------- 1 | Yes | 123 2 | No | 123 3 | No | 123 4 | Yes | 123 5 | Yes | 123 1 | No | 124 2 | Yes | 124 3 | No | 124 4 | No | 124 5 | No | 124

Третий содержит другую информацию о родительском

Parent | Parent Title ---------------------------------------- 123 | Questionnaire Sample 1 124 | Questionnaire Sample 2

В идеале я хотел бы создать представление, которое переносит или поворачивает данные в более удобный формат, я думаю что-то вроде:

Parent | Question 1 | Question 2 | Question 3 | Question 4 | Question 5 123 | Yes | No | No | Yes | Yes 124 | No | Yes | No | No | No

Я нашел пару действительно хороших потоков здесь, с которыми я работал:

Простой способ переноса столбцов и строк в Sql?

T-SQL Pivot/Unpivot (Transpose) Заголовки столбцов, необходимые как строки данных

Был еще один:

Транспонирование строк в столбцы в SQL Server 2008 R2

что я вытащил следующее из:

SELECT [First_Set], [Second_Set] FROM ( SELECT B.ColumnName, A.Value , row_number() over(partition by a.Table2ID order by a.Value) seq FROM Table_1 AS A INNER JOIN Table_2 AS B ON A.Table2ID = B.ID ) AS P PIVOT ( min(P.Value) for P.ColumnName in ([First_Set], [Second_Set]) ) AS PIV;

Затем я изменил это значение:

SELECT [PARENT], [QUESTION], [ANSWER] FROM ( SELECT B.PARENT_TITLE, C.QUESTION, A.ANSWER , row_number() over(partition by a.PARENT order by a.PARENT) seq FROM answerTable AS A INNER JOIN parentTable AS B ON A.PARENT = B.PARENT INNER JOIN questionTable AS C ON A.QID = C.QID ) AS P PIVOT ( min(P.RESULT) for P.PARENT in ([PARENT], [QUESTION], [ANSWER]) ) AS PIV;

Возвращает, но все еще не так, как мне нужно.

Невозможно создать новые таблицы, так что в идеале я ищу динамические средства здесь, это для отчетности, так что представление было бы самым легким/лучшим на мой взгляд, но я открыт для предложений.

Спасибо.

qaru.site

SQL: Транспонирование таблиц(?) - Меркантильный гуру

March 24th, 2010

02:42 am - SQL: Транспонирование таблиц(?)Когда для некоторой строчки может дополнительно быть произвольное количество произвольных дополнительных полей - хорошо их вынести в отдельную табличку вида tbl(id*, parent, field, value). Однако потом, иногда, хочется получить их для определенного parent не ввиде таблицы, а ввиде строки. Если набор полей так или иначе известен - можно сделать

SELECT (value FROM tbl WHERE parent=? AND field=?) AS field_1,(value FROM tbl WHERE parent=? AND field=?) AS field_2,[...];

Однако это и не оптимально, и некрасиво, и требует знания всех полей. Есть ли более оптимальный и красивый способ получить их ввиде строки (переписать то же самое на JOIN будет намного менее красивым, хотя более оптимальным) и можно ли обойтись без знания зарания набора значений поля field?

Средствами SQL нет.

From:Date:

plumqqz

March 24th, 2010 05:38 am (UTC)

В чем проблемы?

(Link)

pivot_clause The pivot_clause lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. The output of a pivot operation typically includes more columns and fewer rows than the starting data set.

Ну т.д.

Завести поле со значением во всех ячекйках, например "0" и переворачивать относительно него. Если нужно подробнее - я посмотрю как мы делали это в нашей программе.

LiveJournal.com

knutov.livejournal.com

Транспонирование последовательности целых чисел в таблицу MS SQL Server

Таблица содержит только один столбец с последовательностью целых чисел:

Можно ли перенести его в таблицу с тремя столбцами только с одним запросом? Таким образом, результат должен быть:

Пытаться:

with cte as (select myCol, floor((row_number() over (order by myCol)-1)/3) rn, (row_number() over (order by myCol)-1) % 3 + 1 cn from myTable) select [1],[2],[3] from (select * from cte) as src pivot (max(myCol) for cn in ([1],[2],[3])) as pvt

SQLFiddle здесь .

Попробуй это –

DECLARE @temp TABLE (id INT) INSERT INTO @temp (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (11),(12),(13),(14),(15),(16),(17),(18),(19),(20) SELECT [1], [2], [3] FROM ( SELECT id , rn = (ROW_NUMBER() OVER (ORDER BY id) - 1) % 3 + 1 , rn2 = (ROW_NUMBER() OVER (ORDER BY id) - 1) / 3 FROM @temp ) t PIVOT ( MAX(id) FOR rn IN ([1], [2], [3]) ) pvt

Вот одно из решений:

declare @t table (n int) insert into @t values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13) select n0, n1, n2 from ( select n0, r from ( select n [n0], row_number() over (order by n) - 1 [r] from @t ) t0 ) tt0 left join ( select n1, r from ( select n [n1], row_number() over (order by n) - 1 [r] from @t ) t1 where t1.r % 3 = 1 ) tt1 on (tt1.r / 3) = tt0.r / 3 left join ( select n2, r from ( select n [n2], row_number() over (order by n) - 1 [r] from @t ) t2 where t2.r % 3 = 2 ) tt2 on (tt2.r / 3) = tt0.r / 3 where tt0.r % 3 = 0

Если по последовательности вы имеете в виду, что значения просто увеличиваются на 1, вы можете в основном сделать это с помощью арифметики:

select mycol, (case when mycol + 1 <= maxval then mycol + 1 end), (case when mycol + 2 <= maxval then mycol + 2 end) from myTable cross join (select min(myCol)%3 as offset, max(myCol) as maxval from myTable ) const where (mycol - offset)%3 = 0;

Другие решения более общие – они будут поворачивать любые значения в последовательности. Это специально определяет только числа, которые увеличиваются на 1.

sqlserver.bilee.com