Объединение большого набора данных с помощью Keycolumns

Я иметь набор данных, который выглядит так:

Column1 (PK) | Attribute | Value
1            | Name      | Carl
1            | Jobtitle  | Driver
2            | Name      | Lisa
2            | Age       | 15
3            | Name      | Jon
3            | Age       | 43
3            | Jobtitle  | Programmer

Поскольку атрибуты являются произвольными, их нельзя должным образом распределить по отдельным столбцам. Набор данных может стать и уже огромен.

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

Набор результатов

Column1 (PK) | FusionedAttributes
1            | Name = Carl, Jobtitle = Driver
2            | Name = Lisa, Age = 15
3            | Name = Jon, Age = 43, Jobtitle = Programmer

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

Есть ли возможность это сделать?

SQL Server может возвращать данные в виде сложного XML с 2005 года. В SQL Server 2016 добавлена ​​поддержка JSON. Вы В самом деле хотите вернуть данные в этой форме? JSON или XML значительно упростили бы клиентам работу с этими данными. Это также облегчило бы запрос данных атрибута/значения.

Panagiotis Kanavos 22.05.2019 16:31

В любом случае вы, вероятно, не должен используете такую ​​схему EAV. Разреженные столбцы SQL Server и хранилище XML/JSON означают, что вы можете иметь тысячи необязательных строк в таблице, не занимая при этом места, или хранить объекты со сложными значениями в полях. Схема EAV не может быть проиндексирована или проверена

Panagiotis Kanavos 22.05.2019 16:34
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
0
2
28
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вы не можете сделать это эффективно, но вы можете сделать:

select c.id,
       stuff( (select concat(', ', attribute, ' = ', value)
               from t t2
               where t2.id = c.id
               for xml path(''), type).value('.', 'nvarchar(max)')
              ), 1, 2, ''
            ) as FusionedAttributes 
from (select distinct column1 from t) c;
CREATE table foo (


id int,
attribute varchar(20),
value varchar(20)

)
INSERT into foo
VALUES
(1,'Name', 'Carl'),
(1,'Jobtitle', 'Driver'),
(2,'Name', 'Lisa'),
(2,'Age', '15'),
(3,'Name', 'Jon'),
(3,'Age', '43'),
(3,'Jobtitle  ', 'Programmer')

SELECT e.id, STUFF((SELECT  ', ' + attribute+'='+value
     FROM foo EE
     WHERE  EE.id=E.id

 FOR XML PATH('')), 1, 1, '') AS listStr

FROM foo E
GROUP BY E.id
Ответ принят как подходящий

SQL Server может возвращать данные в виде сложного XML с 2005 года. В SQL Server 2016 добавлена ​​поддержка JSON. Возврат данных в одной из этих форм облегчит клиентским приложениям работу с ними без создания собственного парсера. Это также позволит использовать значения в других запросах T-SQL.

Учитывая эту таблицу:

declare @foo table (
id int,
attribute varchar(20),
value varchar(20)
)

INSERT into @foo
VALUES
(1,'Name', 'Carl'),
(1,'Jobtitle', 'Driver'),
(2,'Name', 'Lisa'),
(2,'Age', '15'),
(3,'Name', 'Jon'),
(3,'Age', '43'),
(3,'Jobtitle  ', 'Programmer')

Вы можете получить атрибуты в виде XML с помощью:

select 
    f1.id, 
    ( select attribute as name,value 
      from @foo attribute 
      where attribute.id=f1.id 
      for xml auto,type,Root('record'))
from @foo f1
group by f1.id

Это возвращает:

id  DataAsXML
1   <record><attribute name="Name" value="Carl" /><attribute name="Jobtitle" value="Driver" /></record>
2   <record><attribute name="Name" value="Lisa" /><attribute name="Age" value="15" /></record>
3   <record><attribute name="Name" value="Jon" /><attribute name="Age" value="43" /><attribute name="Jobtitle  " value="Programmer" /></record>

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

mayen 23.05.2019 13:42

@mayen вам следует ознакомиться с различными статьями MVP SQL Server об этом, например, SQL: Дизайн — Таблицы значений атрибутов объектов (часть 1) — Почему? Грег Лоу и SQL: Дизайн — Таблицы значений атрибутов объектов (Часть 2) — Плюсы и минусы. Подсказка: им это не нравится

Panagiotis Kanavos 23.05.2019 13:49

Я буду. Это здорово, но структуры данных, которые уже содержат данные, часто сложно реструктурировать. Однако, если есть узкие места в производительности, нужно найти что-то еще. Созданный вами запрос дает требуемые результаты, но они огромны ^^

mayen 23.05.2019 14:12

@mayen это неотъемлемая проблема работы с EAV. Единственный способ получить нет огромные результаты — прочитать данные в их форме EAV и восстановить объекты на клиенте. В SQL Server 2016 вы можете использовать JSON, который будет давать результаты лишь немного больше, чем вы пробовали, хотя они будут выглядеть некрасиво. Функция разреженных столбцов SQL Server в большинстве случаев устраняет необходимость в EAV — зачем использовать EAV, если вы можете иметь 30 000 столбцов в таблице? Внутренне значения являются хранятся в поле XML, поэтому вы платите только за значения, отличные от NULL.

Panagiotis Kanavos 23.05.2019 14:18

Другие вопросы по теме