SQL Использование PIVOT для многозначных атрибутов

Я разработал таблицу EAV, которая выглядит так:

SID  AID  VID
1     1    1
1     2    1
1     3    2
1     4    3
1     1    2

SID означает идентификатор субъекта, AID означает идентификатор атрибута, а VID означает ValuedID.

также таблица для сопоставления атрибутов:

AttributeID AttributeName
    1            Hobbies
    2            Name
    3            Gender
    4            IrisColor

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

SELECT
    SubjectID,
    Hobbies,
    Name,
    Gender,
    IrisColor       
FROM
(
SELECT SubjectID, attr.AttributeName as attribute, ValueID from SubjectDetails, SubjectAttributes as attr WHERE SubjectDetails.AttributeID=attr.ID
) as t
PIVOT(
MAX(ValueID) 

FOR attribute IN (Hobbies,Name,Gender,IrisColor)) AS t1

WHERE SubjectID=1

Я получаю это:

SubjectID Hobbies Name Gender IrisColor
    1        1      1     2      3

Это почти правильно, но SubjectAttribute 1 (то есть хобби) появляется еще раз в первой таблице (SubjectDetails), поэтому я хочу добиться следующего:

SubjectID Hobbies Name Gender IrisColor
    1        **1,2**      1     2      3

Я должен упомянуть, что меня не волнует, какой разделитель используется, и что я пытался сделать это с помощью функции STUFF, но сложно сочетать PIVOT и STUFF (или я просто не знаю, как это сделать). Любые предложения?


database rows sql-server pivot multivalue
person berthos    schedule 25.04.2017    source источник
comment
Вы можете изменить исходную таблицу EAV, чтобы в ней была одна строка для каждого атрибута со значениями, разделенными запятыми, а затем выполнить поворот с помощью сводного запроса.   -  person Rigerta    schedule 25.04.2017
comment
Я не могу из-за этого ... требование состоит в том, чтобы иметь отдельные строки для каждого атрибута, даже если он повторяется. Я только не хочу делать это в представлении, но не так, как при хранении с разделением запятой   -  person berthos    schedule 25.04.2017

Ответы (2)


arrow_upward
1
arrow_downward

Это должно сработать, я сделал следующее: Сохранил информацию из вашей таблицы EAV (table1) в виде одной строки на SID в temporary table (вместо этого вы можете создать view). Затем поверните этот набор результатов, как показано ниже (используя ваш сводной запрос):

SELECT *   
FROM
  (
     SELECT * from #temptbl
  ) as t
PIVOT( MAX(vid) FOR attrname IN (Hobbies,Name,Gender,IrisColor)) AS t1
WHERE sid=1

Я получил этот результат:

введите здесь описание изображения

Пожалуйста, проверьте полную рабочую версию здесь.

person Rigerta    schedule 25.04.2017
comment
очень хорошо! :) удачного кодирования! - person Rigerta; 25.04.2017
comment
Еще один вопрос, как лучше создать представление или каждый раз пересоздавать таблицу? - person berthos; 25.04.2017
comment
Это зависит от того, но если данные не меняются часто, вы можете держать их в поле зрения. Вы также можете попробовать оба сценария и выяснить, какой из них быстрее! Временная таблица всегда поможет. Вы можете взглянуть на этот ответ (stackoverflow.com/questions/16897323/). - person Rigerta; 25.04.2017
comment
Хорошо, а что касается решения, то я также хотел попробовать заменить функцию «MAX» другой функцией Aggregate, написанной мной, которая объединяет данный столбец. - person berthos; 25.04.2017

arrow_upward
0
arrow_downward

Это будет работать без использования PIVOT, я думаю, здесь не нужно использовать PIVOT

SELECT  SubjectID
        ,+STUFF((SELECT ', '+CAST(ValueID AS NVARCHAR) 
                FROM @T_SubjectAttributes A 
                INNER JOIN @T_SubjectDetails B ON A.AttributeID = B.AttributeId
                WHERE AttributeName = 'Hobbies'
                AND B.SubjectID = H.SubjectID FOR XML PATH(''))
                        ,1,2,'')    AS Hobbies
        ,STUFF((SELECT ', '+CAST(ValueID AS NVARCHAR) 
                FROM @T_SubjectAttributes A 
                INNER JOIN @T_SubjectDetails B ON A.AttributeID = B.AttributeId
                WHERE AttributeName = 'Name' 
                AND B.SubjectID = H.SubjectID FOR XML PATH(''))
                        ,1,2,'')    AS Name
        ,STUFF((SELECT ', '+CAST(ValueID AS NVARCHAR) 
                FROM @T_SubjectAttributes A 
                INNER JOIN @T_SubjectDetails B ON A.AttributeID = B.AttributeId
                WHERE AttributeName = 'Gender' 
                AND B.SubjectID = H.SubjectID FOR XML PATH(''))
                        ,1,2,'')    AS Gender
        ,STUFF((SELECT ', '+CAST(ValueID AS NVARCHAR) 
                FROM @T_SubjectAttributes A 
                INNER JOIN @T_SubjectDetails B ON A.AttributeID = B.AttributeId
                WHERE AttributeName = 'IrisColor' 
                AND B.SubjectID = H.SubjectID FOR XML PATH(''))
                        ,1,2,'')    AS IrisColor
FROM @T_SubjectDetails H
GROUP BY SubjectID
person SHD    schedule 25.04.2017