Сводные точки с динамическими столбцами в SQL Server
Я работаю над SQL-запросом, используя pvots с динамическими столбцами в SQL Server (T-sql). Вместо того, чтобы отправлять длинный запрос, я иллюстрирую свою проблему с помощью упрощенной модели.
Я создаю 2 таблицы: Table1 и Table2 и заполняю их несколькими записями следующим образом:
Table1:
Col_ID1 ............... Col_Name
1 ......................... янв-11
2 .................. ....... 11 фев
3 ......................... 11 марта
Таблица2:
Col_ID2 ...... Account ..... AccountName ...... Amount
1 ............... 121 ......... ..Электричество ............ 10000
2 ............... 121 ............ Электричество .. .......... 20000
3 ............... 121 ........... Электричество ....... ..... 30000
1 ............... 122 ........... Телефон ............ ..100
2 ............... 122 ........... Телефон .............. 200
3 ............... 122 ........... Телефон .............. 300
Я создаю Pivot, но хочу, чтобы имена столбцов генерировались параметрически (на основе дат, введенных с экрана ввода), а не жестко закодированы.
Запрос ниже работает хорошо, но дает только несколько столбцов следующим образом:
Янв-11 ........... Фев-11 ........... Мар -11
10,000,00 ...... 20,000,00 ...... 30,000,00
100,00 ............... 200,00 .......... .300.00
Я хочу, чтобы запрос также возвращал описательные столбцы, как показано ниже:
Учетная запись ...........AccountName ........... 11 января ............ 11 февраля .............. 11 марта
121 ................. Электричество .................. 10,000.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 в моем запросе.
Большое спасибо,
Леон Лай
.
.
.
Вот мой запрос:
------------------------ создать и заполнить table1 ------------ --------------------
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')
------------------------- создать & заполнить table2 ----------------------------------
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
-------------------------- подзапрос -----
(ВЫБРАТЬ
t1.Col_Name,
t2.Account,
t2.Amount
ИЗ Table1 AS t1
ПРИСОЕДИНЯЙТЕСЬ к таблице 2 КАК t2 НА t1.Col_ID1 = t2.Col_ID2
) p
-------------------- pivot --------------- ----------
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, который определит имена заголовков столбцов следующим образом:
Jun-11, Jul-11, Aug-11 ..... мая-12.
Если я введу другую дату, скажем «01 .09.10» (1 сентября 2010 г.), заголовки столбцов изменятся на:
10 сентября, 10 октября, .... 11 августа
Это Кажется, вы жестко запрограммировали заголовки моих столбцов.
Не могли бы вы еще раз взглянуть на мой запрос и предложить что-то, что позволит параметрически генерировать имена столбцов вместо жесткого кодирования?
Спасибо
Леон Лай