JohnIf you have data in an entity-attribute-value model and you want to pivot the data into one row using SQL, there’s usually two approaches I take. The first way – based on T-SQL – is to use the pivot function. The second way – kind of a hack way – is to use a max function on top of a case statement for each attribute you have.
The Data
In an EAV model, data would look something like this:
Using Pivot Function
Using the pivot function in T-SQL, your code would look something like this:
The logic is simple enough to understand, but you would need to kind of double the work to ensure your source query has all the attributes and also list them in the pivot query. You would also need to ensure your aggregate function works with your data. For instance, MAX() wouldn’t work with bits.
Using Max Function and Case Statements
The second way, which is kind of a hack way in my opinion, is to create a case statement for each attribute and then do a max function on each case statement. Here’s the first step broken down:
Adding the max function allows you to pull forward the maximum value for each column/attribute because all the other rows should be blank or null:
This way is a little more intuitive in my opinion and I find that it’s easier to join this data to other queries. This pattern wouldn’t work though if you have more than one row for each attribute per grouping. However, if that’s the case, your EAV model probably needs some changes.
Hopefully that helps. Happy coding!
