SQL Using PIVOT for multi valued attributes

Question

I designed an EAV table that looks like this:

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

SID stand for Subject ID, AID stands for Attribute ID and VID stands for ValuedID

also a table to map the attributes:

AttributeID AttributeName
    1            Hobbies
    2            Name
    3            Gender
    4            IrisColor

After using pivot on the first table, linked to the attribute table:

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

I get this:

SubjectID Hobbies Name Gender IrisColor
    1        1      1     2      3

Which is almost correct, but SubjectAttribute 1 (which is hobbies) appears one more time in the first table (SubjectDetails), so what I want to achieve is this:

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

I have to mention that I don't care about what separator is used and that I tried doing that with the STUFF function but it is a pain to combine PIVOT and STUFF (or I just don't know how).. Any suggestions?

You can modify your source EAV table so that it has one row per attribute with comma separated values and then pivot using your pivot query. — Rigerta, Apr 25 '17 at 07:02
I cannot due that.. the requirement is to have separate rows for each attribute even if it repeats. I only wan't to do that in a view, but not as in storing it with comma separation — Norbert Forgacs, Apr 25 '17 at 08:21

score 1 · Accepted Answer · answered Apr 25 '17 at 08:37

1

This should work, I did the following: Stored the information from your EAV table (table1) as single row per SID into a temporary table (you can create a view instead). Then pivoted that resultset as below (using your pivot query):

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

I got this result:

Please check the full working version here.

answered Apr 25 '17 at 08:37

Rigerta

3,959
15
26

very nice! :) happy coding! – Rigerta Apr 25 '17 at 08:49
One more question, what is the best way, creating a view or recreating the table everytime? – Norbert Forgacs Apr 25 '17 at 08:50
It depends, but if the data does not change often you could keep it in a view. You could also try both scenarios and find out which one is faster! A temp table will do the trick everytime for sure. You can take a look at this answer (http://stackoverflow.com/questions/16897323/what-to-use-view-or-temporary-table). – Rigerta Apr 25 '17 at 08:54
Ok, and about the solution, what I also wanted to try is to replace the 'MAX' function with another Aggregate function written by me which concatenates the given column – Norbert Forgacs Apr 25 '17 at 10:07

score 0 · Answer 2 · answered Apr 25 '17 at 11:06

This would work without using PIVOT, I think here no need to use 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

SQL Using PIVOT for multi valued attributes

2 Answers2