SQL объединяет имена столбцов и типы данных со значениями столбца

Я использую Sql Server 2012 для своей СУБД.

В моей базе данных есть таблица продуктов, которая связана с серией таблиц каталога. Эти таблицы каталогов представляют различные категории продуктов. Идея состоит в том, что, хотя все продукты имеют определенные общие атрибуты (наш внутренний идентификатор для него, поставщик, стоимость и т. Д.), Специфика продуктов будет отличаться (мебель не описывается так же, как описывается цифровой продукт) ,

Кроме того, каждый продукт имеет родительский объект. У родителя может быть несколько детей (или продуктов в этом случае).

Моя задача – выбрать продукты и соответствующую информацию для данного списка идентификаторов родителя и заполнить эту информацию в документе XML.

В настоящее время я работаю над таблицами:

  • ПРОДУКТ
  • PRODUCT_DIGITAL
  • PRODUCT_FURNITURE

Продукт имеет отношения PK / FK с PRODUCT_DIGITAL на Product_Id. Такой же тип отношений существует для PRODUCT to PRODUCT_FURNITURE.

Если продукт выглядит следующим образом:

PRODUCT_ID -- PRODUCT_CATEGORY -- PARENT_ID -- PARENT_TYPE -- DELIVERY_IN_DAYS 100 DIG 1 1 7 101 DIG 1 1 8 102 DIG 1 1 1 103 DIG 2 1 2 104 DIG 2 1 1 

И PRODUCT_DIGITAL выглядит так:

 PRODUCT_ID -- PRODUCT_TYPE -- PRODUCT_NAME -- PRODUCT_MNEMONIC 100 A IMG1 IMAWTRFL 101 B SND1 SNDENGRV 102 B SND2 SNDHRSLF 103 A IMG2 IMGNBRTO 104 B SND3 SNDGTWNE 

В конце концов, я хочу, чтобы набор результатов выглядел так:

 PRODUCT_CATEGORY -- PRODUCT_ID -- PRODUCT_TYPE -- PARENT_ID -- DELIVERY_IN_DAYS -- PROD_EXTENSION_NAME -- PROD_EXTENSION_TYPE -- PROD_EXTENSION_VALUE DIG 100 A 1 7 PRODUCT_NAME STRING IMG1 DIG 100 A 1 7 PRODUCT_MNEMONIC STRING IMAWTRFL DIG 101 B 1 8 PRODUCT_NAME STRING SND1 DIG 101 B 1 8 PRODUCT_MNEMONIC STRING SNDENGRV DIG 102 B 1 1 PRODUCT_NAME STRING SND2 DIG 102 B 1 1 PRODUCT_MNEMONIC STRING SNDHRSLF DIG 103 A 2 2 PRODUCT_NAME STRING IMG2 DIG 103 A 2 2 PRODUCT_MNEMONIC STRING IMGNBRTO DIG 104 B 2 1 PRODUCT_NAME STRING SND3 DIG 104 B 2 1 PRODUCT_MNEMONIC STRING SNDGTWNE 

Мой первоначальный поиск привел меня к UNPIVOT – но пока мне не удалось обернуться. То, что я закончил, это создать временную таблицу и обновить ее в проходах, а затем вернуться к таблице продуктов для окончательного выбора:

 create table #tbl(product_id int, prod_extension_name varchar(100), prod_extension_type varchar(100), prod_extension_value varchar(1000)) insert into #tbl select p.product_id, c.column_name, case c.data_type when 'varchar' then 'string' else data_type end as data_type , null from dbo.product p, information_schema.columns c where c.table_name = 'PRODUCT_DIGITAL' and c.column_name in ('PRODUCT_NAME','PRODUCT_MNEMONIC') update #tbl set prod_extension_value = p.product_name from dbo.product p where #tbl.product_id = p.product_id and #tbl.colname = 'PRODUCT_NAME' update #tbl set prod_extension_value = p.product_mnemonic from dbo.product p where #tbl.product_id = p.product_id and #tbl.colname = 'PRODUCT_MNEMONIC' select p.product_category, p.product_id, pd.product_category, #tbl.prod_extension_name, #tbl.prod_extension_type, #tbl.prod_extension_value from dbo.product p inner join dbo.product_digital pd on p.product_id = pd.product_id inner join #tbl on p.product_id = #tbl.product_id order by product_id 

Может ли кто-нибудь указать мне на лучший способ сделать это? Кажется, я должен быть в состоянии сделать это быстрее, без необходимости делать несколько обновлений и т. Д.

Я думаю, что это будет делать то, что вы хотите. Это реализует функцию UNPIVOT а затем получает информацию столбца из представления information_schema.columns чтобы получить результат:

 select product_id, product_category, parent_id, delivery_in_days, PROD_EXTENSION_NAME, case when c.data_type = 'varchar' then 'STRING' else null end as PROD_EXTENSION_TYPE, PROD_EXTENSION_VALUE from ( select p.product_id, p.product_category, p.parent_id, p.delivery_in_days, PRODUCT_NAME, PRODUCT_MNEMONIC from product p left join product_digital pd on p.product_id = pd.product_id ) src unpivot ( PROD_EXTENSION_VALUE for PROD_EXTENSION_NAME in (PRODUCT_NAME, PRODUCT_MNEMONIC) ) up inner join ( select c.column_name, c.data_type from information_schema.columns c where c.table_name = 'PRODUCT_DIGITAL' and c.column_name in ('PRODUCT_NAME','PRODUCT_MNEMONIC') ) c on up.PROD_EXTENSION_NAME = c.column_name 

См. SQL Fiddle with Demo

Результат:

 | PRODUCT_ID | PRODUCT_CATEGORY | PARENT_ID | DELIVERY_IN_DAYS | PROD_EXTENSION_NAME | PROD_EXTENSION_TYPE | PROD_EXTENSION_VALUE | ----------------------------------------------------------------------------------------------------------------------------------- | 100 | DIG | 1 | 7 | PRODUCT_NAME | STRING | IMG1 | | 100 | DIG | 1 | 7 | PRODUCT_MNEMONIC | STRING | IMAWTRFL | | 101 | DIG | 1 | 8 | PRODUCT_NAME | STRING | SND1 | | 101 | DIG | 1 | 8 | PRODUCT_MNEMONIC | STRING | SNDENGRV | | 102 | DIG | 1 | 1 | PRODUCT_NAME | STRING | SND2 | | 102 | DIG | 1 | 1 | PRODUCT_MNEMONIC | STRING | SNDHRSLF | | 103 | DIG | 2 | 2 | PRODUCT_NAME | STRING | IMG2 | | 103 | DIG | 2 | 2 | PRODUCT_MNEMONIC | STRING | IMGNBRTO | | 104 | DIG | 2 | 1 | PRODUCT_NAME | STRING | SND3 | | 104 | DIG | 2 | 1 | PRODUCT_MNEMONIC | STRING | SNDGTWNE | 

Редактировать # 1: Если вы хотите динамически преобразовывать этот тип преобразования, вам нужно будет использовать динамический SQL. для этого вы будете использовать следующее.

Во-первых, вам нужно будет получить список столбцов в UNPIVOT :

 select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('PRODUCT_DIGITAL') and C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE') -- include the items you DO NOT want to unpivot for xml path('')), 1, 1, '') 

Окончательный сценарий будет:

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('PRODUCT_DIGITAL') and C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE') for xml path('')), 1, 1, '') select @cols = stuff((select ', '''+C.name + '''' from sys.columns as C where C.object_id = object_id('PRODUCT_DIGITAL') and C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE') for xml path('')), 1, 1, '') set @query = ' select product_id, product_category, parent_id, delivery_in_days, PROD_EXTENSION_NAME, case when c.data_type = ''varchar'' then ''STRING'' else null end as PROD_EXTENSION_TYPE, PROD_EXTENSION_VALUE from ( select p.product_id, p.product_category, p.parent_id, p.delivery_in_days, PRODUCT_NAME, PRODUCT_MNEMONIC from product p left join product_digital pd on p.product_id = pd.product_id ) src unpivot ( PROD_EXTENSION_VALUE for PROD_EXTENSION_NAME in ('+ @colsunpivot +') ) up inner join ( select c.column_name, c.data_type from information_schema.columns c where c.table_name = ''PRODUCT_DIGITAL'' and c.column_name in ('[email protected]+') ) c on up.PROD_EXTENSION_NAME = c.column_name' exec(@query) 

См. SQL Fiddle with Demo . Это приведет к тому же результату, что и исходная версия, за исключением того, что она динамическая.

Я мог бы прыгать с пистолета, но вы говорите, что ваш конечный результат должен быть XML. Хотя я не включил все ваши требования, что-то вроде этого соответствует вашим требованиям? (Я думаю, что я, возможно, упростил ваши требования)

 ;WITH PRODUCT (PRODUCT_ID, PRODUCT_CATEGORY, PARENT_ID, PARENT_TYPE, DELIVERY_IN_DAYS) AS ( SELECT 100, 'DIG', 1, 1, 7 UNION ALL SELECT 101, 'DIG', 1, 1, 8 UNION ALL SELECT 102, 'DIG', 1, 1, 1 UNION ALL SELECT 103, 'DIG', 2, 1, 2 UNION ALL SELECT 104, 'DIG', 2, 1, 1 ) ,PRODUCT_DIGITAL (PRODUCT_ID, PRODUCT_TYPE, PRODUCT_NAME, PRODUCT_MNEMONIC) AS ( SELECT 100, 'A', 'IMG1', 'IMAWTRFL' UNION ALL SELECT 101, 'B', 'SND1', 'SNDENGRV' UNION ALL SELECT 102, 'B', 'SND2', 'SNDHRSLF' UNION ALL SELECT 103, 'A', 'IMG2', 'IMGNBRTO' UNION ALL SELECT 104, 'B', 'SND3', 'SNDGTWNE' ) SELECT P.PRODUCT_CATEGORY ,P.PRODUCT_ID ,P.PARENT_TYPE ,P.PARENT_ID ,P.DELIVERY_IN_DAYS ,PD.PRODUCT_NAME ,PD.PRODUCT_MNEMONIC FROM PRODUCT P JOIN PRODUCT_DIGITAL PD ON P.PRODUCT_ID = PD.PRODUCT_ID FOR XML PATH 
  • SQL-сервер, как преобразовать значения столбцов в строки
  • поворот строк в столбцы в tsql
  • Получите координаты X и Y каждой ячейки из таблицы и преобразуйте ее в новую таблицу на сервере sql
  • SQL Query столбцы для строк - Unpivot?
  • Возможно ли иметь несколько опорных точек с использованием одного и того же столбца поворота с помощью SQL Server
  • Сортировка данных для источника PIVOT
  • Unpivot по агрегированным столбцам
  • Ошибка SQL Pivot
  • Использование значения столбца в качестве имени столбца в подзапросе
  • Синтаксическая ошибка при использовании Convert внутри PIVOT
  • Транспонирование столбцов в строки SQL Server
  • Interesting Posts

    C # FIxing Цикл Foreach без значений

    Как я могу получить резервную копию SQL Server 2005 из базы данных SQL Server 2008?

    Как бы вы оптимизировали эту таблицу SQL Server 2008 R2

    Я пытаюсь написать SQL-вложенный запрос, который находит / использует максимальное значение, чтобы найти запись непосредственно перед максимальным значением

    Предотвращение кэширования в SQL Server

    Есть ли способ для столбца SQL ORDER BY ASCENDING с определенными результатами выше ORDER BY?

    Улучшение производительности базы данных SQL Server

    SQL Network Interfaces, ошибка: 50 – Произошла ошибка локальной базы данных. Невозможно создать автоматический экземпляр

    Выбор SQL с приоритетом

    Выполнение сценариев публикации с SQLCMD не возвращается

    Невозможно добавить сборку SQLCLR в базу данных, Msg 300

    SQL Query – понимание синтаксиса

    Захват неудачных значений INSERT

    Процент возвращаемой стоимости подкатегории в SQL

    TSQL-csv для столбца, а не для остальных

    Давайте будем гением компьютера.