SQL Server PIVOT on key-value table

Question

I have a table that has attributes based on a key-value. Example:

CREATE TABLE ObjectAttributes ( int objectId, key nvarchar(64), value nvarchar(512) )

When I select from this I get:

objectId key value ---------------------------- 1 Key 1 Value 1 1 Key 2 Value 2

I was wondering if I could use the PIVOT syntax to turn this into:

objectId Key 1 Key 2 --------------------------- 1 Value 1 Value 2

I know all of my tables will have the same keys. (Unfortunately I cannot easily change the table structure. This is what is leading me to attempt using PIVOTS).

The big issue here though is that pivots require an aggregation function to be used. Is there a way to avert this? Am I completely wrong attempting this? Or is there a better solution?

Do you want fixed column output? That is, you want to pivot all keys for given objectid? — gbn
– gbn, Commented Dec 29, 2010 at 16:54

gbn · Accepted Answer · 2010-12-29 17:05:44Z

A pivot will be no faster then repeated self joins for a fixed column output.

SELECT T1.objectID, T1.Value AS Key1, T2.Value AS Key2 FROM ObjectAttributes T1 JOIN ObjectAttributes T2 ON T1.objectID = T2.objectID WHERE T1.key = 'Key 1' AND T2.key = 'Key 2'

If you want to use PIVOT, then just use MAX. Because you have one row per object/key it's trivial anyway and is there to satisfy the PIVOT requirement.

If you want to PIVOT an unknown number of rows into columns, then it's dynamic SQL (as per SQL Server 2000 solutions) or do it in the client code.

If each object has a fixed number of attributes then I would consider having a 2nd table with real columns maintained by a trigger. Clumsy, but makes life easier for reading

Ah ok. I was assuming the pivot table had some kind of magic behind it that made it quicker. I will just go with the self join route.
You might want to use outer joins for this solution. If you're missing one key then the whole row of output objectIDs will disappear with the inner joins used here.

Sir Wobin · Accepted Answer · 2010-12-29 17:03:31Z

No, you can't avoid the aggregate. SQL Server needs some way of combining the many possible rows into one value. You happen to have one value but the PIVOT functionality is made with many rows in mind.

SELECT objectId, [Key 1], [Key 2] FROM (SELECT objectId, [key], value FROM ObjectAttributes) AS source PIVOT ( MIN(value) FOR [key] IN ([Key 1], [Key 2]) ) as pvt

giò · Accepted Answer · 2019-06-13 07:44:36Z

I am adding this answer since this thread is one of the first that appears on Google for this question.

The simplest solution that I have found is to use the combination of max with case, like this:

-- Pivot the data with a handwritten T-SQL statement. -- Make sure you have an index defined on the grouping column. SELECT RecordID, -- Spreading and aggregation phase MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName', MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName', MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City', MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country' FROM EAVTable GROUP BY RecordID -- Grouping phase

Ref: https://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/

This solution also works pretty much everyone, and you don't need to a lot of join if you need to pivot many columns

What is the reason for the use of the 'MAX' function ? Thanks

Genghiz Khan · Accepted Answer · 2020-12-12 15:07:22Z

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(your_key_column) from YOUR_ORIGINAL_KEY_AND_VALUE_TABLE group by your_key_column FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT your_row_heading_columns,' + @cols + ' INTO YOUR_NEW_PIVOTED_TABLE from ( select your_row_heading_columns,your_key_column,your_value_column from YOUR_ORIGINAL_KEY_AND_VALUE_TABLE ) x pivot ( max(your_value_column) for your_key_column in (' + @cols + ') ) p ' execute sp_executesql @query;

Collectives™ on Stack Overflow

SQL Server PIVOT on key-value table

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Related