Динамически добавлять столбцы во временную таблицу на основе строк в другой временной таблице.

Я пытаюсь создать временную таблицу с динамическим количеством столбцов на основе количества строк из другой временной таблицы. Скажем, у меня есть 89 строк в #table1, в #table2 я хотел бы использовать количество строк и взять соответствующее значение строки в качестве имени столбца. Я возился с этим некоторое время, но я продолжаю получать ошибки. Вот мой запрос, который позже будет преобразован в процедуру.

Моя таблица выглядит следующим образом (все столбцы представляют собой varchar с нулевым значением, если импортированная дата не имеет данных для этого номера CVE — CVEId относится к CVEID ограничения FK в таблице CVENumber):

CVEId     D20160901     D20160902     D20160903     D20160904     D20160905
1         6182          6473          5879          NULL          NULL
2         72862         76583         NULL          NULL          74772

CVENumber Table:
CVEID     CVENumber
1         CVE-781-2016
2         CVE-006-2016

Я надеюсь получить дату столбца или, возможно, использовать введенную дату в качестве первой строки - запустить запрос к этим данным, где я могу указать 01-09-2016 TO 03-09-2016. И вернуть все строки из таблицы с номером CVENumber, указанным в таблице CVENumber. Как я хочу, чтобы мой результат выглядел так:

CVE Number     09-01-2016     09-02-2016     09-03-2016
CVE-781-2016         6182           6473           8579
CVE-006-2016        72682          76583              0

Я надеюсь, что это проясняет, что я пытаюсь сделать.

Мой текущий запрос с использованием STUFF(), который берет строки из #FixedDates и превращает их в столбцы. Я хочу, чтобы эти столбцы, возвращенные в @cols, были добавлены в качестве столбцов в #query_results.

Set nocount on

Insert #tmp
EXEC sp_columns @table_name = N'CVECountsByDate'

-- Using collate to force the DB to only look at Uppercase values
DECLARE @cols varchar(max), @query varchar(max), @cols2 varchar(MAX)

INSERT #FixedDays
SELECT Replace(COLUMN_NAME, 'D' collate Latin1_General_CS_AS, '' collate Latin1_General_CS_AS) from #Tmp 
WHERE COLUMN_NAME LIKE 'D%' collate Latin1_General_CS_AS OR COLUMN_NAME = 'CVEId' ORDER BY COLUMN_NAME DESC


SET @cols = STUFF((SELECT ',' + QUOTENAME(QT.COLUMN_NAME) + ' varchar(100)'
                 FROM #FixedDays QT
                 GROUP BY QT.COLUMN_NAME
                 ORDER BY QT.COLUMN_NAME
                 FOR XML PATH(''), TYPE
                 ).value('.', 'VARCHAR(MAX)')
                 ,1,1,'')

SET @cols2 = N'CREATE TABLE #query_results (' + @cols + ') '
--EXEC(@cols2)
SELECT @cols2

DROP TABLE #FixedDays
DROP TABLE #Tmp

sql-server temp-tables dynamicquery
person bbcompent1    schedule 23.01.2017    source источник
comment
Почему вы не хотите, чтобы это были строки вместо новых столбцов? Имея нормализованные данные для работы, гораздо проще делать все, что вам нужно делать с этими данными.   -  person Sean Lange    schedule 23.01.2017
comment
Формат этих столбцов [не мой дизайн] похож на формат даты, но примерно такой: 1 сентября 2016 года в имени столбца — D20160901. Я пытаюсь взять это имя столбца и преобразовать его в 09-01-2016. Хранимая процедура используется для создания отчета. Я хочу создать временную таблицу с датами в правильном формате и передать два параметра для дат начала и окончания.   -  person bbcompent1    schedule 23.01.2017
comment
Похоже, динамическая сводка или динамическая перекрестная таблица были бы лучшим решением, чем попытка настроить временную таблицу таким образом.   -  person Sean Lange    schedule 23.01.2017
comment
У вас есть идеи, как я могу это сделать? Я не силен в сводных таблицах. Может ли таблица PIVOT возвращать результаты для моего запроса отчета?   -  person bbcompent1    schedule 23.01.2017
comment
Это будет запрос для вашего отчета. Вот отличный пример этого. stackoverflow.com/questions/10404348/< /а>   -  person Sean Lange    schedule 23.01.2017
comment
Извините, я просто не мог заставить это работать вообще. Это либо даст мне первый столбец, либо выдаст ошибку о фигурных скобках, имейте в виду, что в моем запросе не было фигурных скобок. Я использовал то, что у них там было, и изменил запрос, чтобы он соответствовал моим параметрам.   -  person bbcompent1    schedule 24.01.2017
comment
Часть проблемы заключается в том, что вы пометили это как сервер sql, но у вас есть диалект mysql в вашем запросе. (LIMIT 1 OFFSET не является стандартным sql и работает только с mysql). Но для любой реальной помощи с кодом нам нужны подробности, как описано в ссылке, которую я разместил ранее.   -  person Sean Lange    schedule 24.01.2017
comment
Хорошо, так что часть скрипта, который я использую, применима только к MySQL? Хорошо, это имеет смысл. Исходный источник информации заявил, что это для SQL Server; видимо афиша не знала о чем речь. Я продолжу работать над этим и дам вам знать, если столкнусь с чем-то еще. Спасибо.   -  person bbcompent1    schedule 25.01.2017
comment
??? В том, что вы написали, нет динамического sql. И у вас все еще есть синтаксис, пересекающийся между сервером sql и mysql. Честно говоря, весь подход к добавлению таких столбцов полностью ошибочен. Вы должны делать динамическую перекрестную таблицу или динамическую сводку и забыть о концепции добавления этих столбцов в свою временную таблицу. Если вам действительно нужна помощь, вам нужно опубликовать некоторую информацию. Определения таблиц, образцы данных и т. д. И простое перефразирование сообщения об ошибке не слишком полезно. Вот отличное место для начала. spaghettidba.com /2015/04/24/   -  person Sean Lange    schedule 25.01.2017
comment
Никто не говорил вам перепроектировать вашу базу данных. Я предположил, что добавление новых столбцов в ваш темп, как это, ведет к проигрышной битве. Вместо этого вы должны использовать динамическую перекрестную таблицу или динамическую сводную таблицу ВМЕСТО этой временной таблицы. Или используйте это, чтобы СОЗДАТЬ временную таблицу. Делать это в цикле - неправильный путь. Удачи в выяснении этого, я надеюсь, что вы сможете найти решение, которое работает для вас.   -  person Sean Lange    schedule 25.01.2017
comment
О, хорошо, я вижу, что вы сейчас говорите. Мне нужно добавить столбец, который делает идентификатор уникальным. Попался, теперь у меня настроена моя временная таблица, в которой у меня есть RecID в качестве первичного ключа удостоверения. Меня расстроило то, что в примере было два столбца, а у меня был только один. Теперь, когда я добавил этот второй столбец, я посмотрю, как он пойдет. Извините, я расстроился.   -  person bbcompent1    schedule 25.01.2017
comment
Надеюсь, вы получите решена. Если бы вы могли потратить 10 минут, чтобы собрать воедино информацию, необходимую для помощи другим, это было бы довольно просто. Но без этих деталей это просто невозможно.   -  person Sean Lange    schedule 25.01.2017
comment
Хорошо, использование STUFF дало мне строки в качестве вывода в столбцы; однако я не могу добавить эти столбцы с разделителями, используя EXEC(@sql). Если я выберу, переменная выводит запрос, который я могу запустить в окне. Если я попытаюсь выполнить его, он никогда не создаст таблицу.   -  person bbcompent1    schedule 01.02.2017
comment
На самом деле он создает временную таблицу. Но объем временной таблицы ограничен вашим динамическим sql, поэтому, как только вы закончите выполнение, временная таблица будет удалена.   -  person Sean Lange    schedule 01.02.2017
comment
Итак, что вы рекомендуете мне делать в этом случае? Создать реальную таблицу, а затем удалить ее в конце процедуры?   -  person bbcompent1    schedule 01.02.2017
comment
Опять же.... Я рекомендую полностью изменить ваш подход. Используйте динамическую сводку вместо этих сумасшедших манипуляций с вашей временной таблицей.   -  person Sean Lange    schedule 01.02.2017
comment
Я сделал динамический поворот; однако я пытаюсь сослаться на CVEId из таблицы CVEDetails, вытащить номер CVE (т. е. CVE-218-2016). Как бы я это сделал, используя динамическую ось? Нужно ли соединение на основе CVEId из обеих таблиц? У вас есть пример, показывающий соединение в повороте?   -  person bbcompent1    schedule 02.02.2017

Ответы (1)


arrow_upward
0
arrow_downward

Вот что я в итоге сделал...

CREATE TABLE #Tmp
(TABLE_QUALIFIER varchar(40),
  TABLE_OWNER varchar(20),
  TABLE_NAME varchar(40),
  COLUMN_NAME varchar(40),
  DATA_TYPE int,
  TYPE_NAME varchar(20),
  PREC int, LENGTH int,
  SCALE int, RADIX int,
  NULLABLE char(4),
  REMARKS varchar(128),
  COLUMN_DEF varchar(40),
  SQL_DATA_TYPE int,
  SQL_DATETIME_SUB int,
  CHAR_OCTET_LENGTH int,
  ORDINAL_POSITION int,
  IS_NULLABLE char(4),
  SS_DATA_TYPE int)
CREATE TABLE #FixedDays  
 (COLUMN_NAME varchar(40))
CREATE TABLE #query_results
(CVENumber varchar(100) null)
Set nocount on
Insert #tmp
EXEC sp_columns @table_name = N'CVECountsByDate'
INSERT #FixedDays
SELECT Replace(COLUMN_NAME, 'D' collate Latin1_General_CS_AS, '' collate Latin1_General_CS_AS) from #Tmp 
WHERE COLUMN_NAME LIKE 'D%' collate Latin1_General_CS_AS OR COLUMN_NAME = 'CVEId'
DECLARE @listStr VARCHAR(MAX)
DECLARE @FixedList VARCHAR(MAX)
SELECT @FixedList = COALESCE(@FixedList,'[') + COLUMN_NAME + '] VARCHAR(MAX) NULL, [' FROM #FixedDays
SELECT @FixedList = substring(@FixedList, 1, len(@FixedList) -1)
SET @FixedList = LEFT(@FixedList, len(@FixedList)-1) -- Altered Column List
EXEC(N'ALTER TABLE #query_results ADD ' + @FixedList + '')
INSERT INTO #query_results 
SELECT CVEDetails.CVENumber, CVECountsByDate.* FROM CVECountsByDate INNER JOIN CVEDetails ON  CVECountsByDate.CVEId = CVEDetails.CVEID
ALTER TABLE #query_results DROP column CVEId

DROP TABLE #query_results
DROP TABLE #Tmp
DROP TABLE #FixedDays
person bbcompent1    schedule 01.02.2017