Сводки с динамическими столбцами в SQL Server

Question

Сводки с динамическими столбцами в SQL Server

Я работаю над запросом SQL, используя pvots с динамическими столбцами в SQL Server (T-sql). Вместо того, чтобы отправлять мой длинный запрос, я иллюстрирую свою проблему с помощью упрощенной модели.

Я создаю 2 таблицы: Table1 и Table2 и заполняю их несколькими записями следующим образом:

Таблица 1:

Col_ID1............... COL_NAME
1......................... Jan-11
2 февраля.........................-11
3......................... Mar-11

Таблица 2:

Col_ID2...... Счет..... AccountName...... сумма
1...............121........... Электричество............ 10000
2............... 121........... Электричество............ 20000
3............... 121........... Электричество............30000
1............... 122........... Телефон.............. 100
2............... 122........... Телефон.............. 200
3............... 122........... Телефон..............300

Я создаю сводную таблицу, но я хочу, чтобы имена столбцов генерировались параметрически (на основе дат, введенных на экране ввода), а не были жестко запрограммированы.

Приведенный ниже запрос работает хорошо, но дает только несколько столбцов:

Jan-11........... Февраль-11........... Mar-11
10.000.00...... 20,000.00...... 30,000.00
100,00 200,00..........................300,00

Я хочу, чтобы запрос также возвращал описательные столбцы, например:

Счет........... AccountName...........Jan-11............ Февраль-11.............. Mar-11
121................. Электричество.................. 10000,00...... 20,000.00.......... 30,000.00
122................. Телефон..................... 100,00...........200.00.............300,00

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

Этот запрос является адаптацией следующей статьи, написанной доктором Андрасом в сентябре 2007 года. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Кто-то заметил, что код может подвергаться атакам с использованием инъекций, и предложил использовать функцию Quotename вместо объединения квадратных скобок.

Не могли бы вы объяснить, как использовать Quotename в моем запросе.

Большое спасибо,

Леон Лай.
,
,

Вот мой запрос:

------------------------ создать и заполнить таблицу1 ---------------------- ----------

CREATE TABLE Table1
(Col_ID1 INT, 
Col_Name varchar(10))  

INSERT INTO Table1 VALUES (1, 'Jan-11')  
INSERT INTO Table1 VALUES (2, 'Feb-11')  
INSERT INTO Table1 VALUES (3, 'Mar-11')

------------------------- создать и заполнить таблицу2 --------------------- -------------

CREATE TABLE Table2  
(Col_ID2 INT,  
Account varchar(10),  
AccountName varchar(20),  
Amount numeric(18,6))  

INSERT INTO Table2 VALUES (1, 121, 'Electricity', 10000)  
INSERT INTO Table2 VALUES (2, 121, 'Electricity', 20000)  
INSERT INTO Table2 VALUES (3, 121, 'Electricity', 30000)  
INSERT INTO Table2 VALUES (1, 122, 'Telephone', 100)        
INSERT INTO Table2 VALUES (2, 122, 'Telephone', 200)   
INSERT INTO Table2 VALUES (3, 122, 'Telephone', 300)

---------------------------------- создать заголовки столбцов ------------- ------

DECLARE @cols NVARCHAR(2000)   
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT   
'],[' + t2.Col_Name   
FROM Table1 AS t2   
ORDER BY '],[' + t2.Col_Name 
FOR XML PATH('')   
), 1, 2, '') + ']'

------------------------------------- создать @query ---------- ------------

DECLARE @query NVARCHAR(4000)   

SET @query = N'SELECT '+   
@cols +' 

FROM

-------------------------- подзапрос -----

(SELECT t1.Col_Name, t2.Account, t2.Amount FROM Table1 AS t1 JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2 ) p

-------------------- пивот -------------------------

PIVOT ( Sum ([Amount] ) FOR Col_Name IN ( '+ @cols +' ) ) AS pvt '

---------------------- exec & drop ----------

EXECUTE(@query)
drop table table1
drop table table2

================================================== =====

Привет филип

Большое спасибо за ваш ответ.

Ваш предложенный запрос работает гладко и генерирует ожидаемый экран, но это не совсем то, что я хотел.

Во-первых, спасибо за код: SELECT @cols = isnull (@cols + ',', ' ') + '[' + Col_Name + ']'

Это проще и заменяет мою строку, включающую материал и путь XML, по-видимому, с тем же эффектом.

Позвольте мне объяснить, что я хочу сделать.

Я хочу разработать запрос в Sap Business 1 (Пакет бухгалтерского учета - или назвать его ERP). Sap использует T-sql в Microsoft Server 2008 и имеет собственный генератор запросов. За очень немногими исключениями Sap sql похож на T-sql.

Я хочу, чтобы мой запрос давал список всех доходов и расходов ежемесячно за 12-месячный период.

Однако я не хочу, чтобы заголовки моих столбцов были жестко закодированы (поскольку это потребовало бы от меня время от времени вносить изменения в свой запрос) следующим образом:

11 января, 11 февраля, 11 марта, 11 апреля,..... 11 декабря

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

Как я уже упоминал, запрос, который я разместил на форуме, является упрощенной версией моего реального запроса, используемого только для иллюстрации. Реальный запрос содержит несколько переменных, и экран ввода (называемый блоком "Запрос - критерии выбора" в Sap b1) позволяет пользователю ввести дату. Именно эта дата будет использоваться для динамического определения имен столбцов.

Вот почему мне понадобились такие сложные инструменты, как @cols, @query, pivot и т. Д.

Если я введу, скажем, "01.06.11" (01 июня 2011 г.) на экране ввода, эта дата будет передана в sql, который определит имена заголовков столбцов следующим образом:

11 июня, 11 июля, 11 августа... 12 мая.

Если я введу другую дату, скажем, "01.09.10" (01 сентября 2010 г.), заголовки столбцов изменятся на:

10 сентября, 10 октября,.... 11 августа

Кажется, вы жестко закодировали заголовки моих столбцов.

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

Спасибо

Леон Лай

9

t-sql sql-server-2008 sql-server-2005 pivot dynamic-data

Источник

user997817 19 окт '11 в 13:26

2 ответа

Другие вопросы по тегам t-sql sql-server-2008 sql-server-2005 pivot dynamic-data

user7491 19 окт '11 в 13:54 2011-10-19 13:54 · Answer 1 · 2011-10-19 13:54

Добавить эти столбцы очень просто. Окончательный запрос будет

SELECT Account, AccountName, [Feb-11],[Jan-11],[Mar-11]   FROM   
(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( [Feb-11],[Jan-11],[Mar-11] )
) AS pvt

для которого t2.AccountName добавлено в подзапрос, а Account и AccountName добавлены в начальный SELECT. Добавьте их в оператор сборки, и все готово:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT Account, AccountName, ' +    @cols +'   FROM   

(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p

PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( '+
@cols +' )
) AS pvt '

Что касается внедрения SQL-кода, я могу видеть, что это происходит только в том случае, если кто-то каким-то образом внедряет вредоносный код в Table1.Col_Name, и если вам приходится беспокоиться об этом, у вас возникают большие проблемы, чем "блокировка" этого динамического запроса.

Также стоит упомянуть, что я бы использовал следующее для построения списка столбцов (@Cols), потому что он короче и проще для чтения, но в основном потому, что я не люблю XML.

DECLARE @cols NVARCHAR(2000)    
SELECT @cols = isnull(@cols + ',', '') + '[' + Col_Name + ']'
 FROM Table1
 ORDER BY Col_Name

user7491 20 окт '11 в 14:41 2011-10-20 14:41 · Answer 2 · 2011-10-20 14:41

Добавление еще одного ответа, так как это редактирование почти второй вопрос. (Без подробностей и подробностей я могу предложить только общие схемы и псевдо-код - я не знаю SAP.)

Давайте начнем с опоры. Он должен генерировать столбцы, помеченные, предположительно, месяцем, который вы имели в своем примере как Table1.Col_Name, varchar(10); эти значения извлекаются и динамически добавляются в сводный запрос в виде имен столбцов. Если в базе данных такого столбца нет, его необходимо построить для запроса на основе данных, введенных пользователем. Я буду работать со следующими предположениями: - У данных есть столбец даты и времени, где можно найти любое значение (от года до миллисекунды) - Пользователь указывает "дату начала" (всегда ли это первое число месяца?), А вы должны генерировать столбцы для этого и последующих 11 месяцев, объединяя данные, которые попадают в каждый целевой месяц.

Шаг 1, я бы настроил и заполнил временную таблицу, содержащую 12 целевых столбцов:

CREATE TABLE #Months
 (
   Col_Name    varchar(10)
  ,MonthStart  datetime
  ,MonthEnd    datetime
)

Метка отформатирована так, как вы хотите, чтобы она отображалась, MonthStart будет абсолютным началом месяца (скажем, 1 октября 2011 г.00: 00: 00.000), а MonthEnd будет абсолютным началом следующего месяца (1 ноября 2011 г.00: 00: 00.000) - это позволяет использовать SELECT … from <table> where DataDate >= MontStart and DataDate < MonthEnd чтобы получить все данные в течение этого месяца.

Затем присоедините эту таблицу к вашей таблице данных и агрегируйте, что-то вроде:

SELECT
   mt.Col_Name
  ,sum(dt.RawData)  Amount
 from #Months mt
  inner join MyData dt
   on dt.DataDate >= mt.MonthStart
    and dt.DataDate < mt.MonthEnd  --  Yes, ON clauses don't have to be simple equivalencies!
  inner join <other tables as necessary for Account, AccountName, etc.>

Подключите это как самый внутренний запрос оператора pivot, извлеките / соберите список Col_Names из временной таблицы, используя не XML-запрос (я не знаю, как еще его вызвать), соберите и выполните динамически, и вы должны будь хорошим.