7/8/17

SQL Server - Pivot Rows to Columns

On systems that are meta-data driven, there may not be a concrete table schema declaration, and the meta-data values can be stored as rows. To provide an example, let’s build a custom_field table that can hold multiple custom values for a particular entity:

Pivoting rows to columns



Setup the table and sample data



create table dbo.custom_fields
(
   ref_id int, 
   field_id int,
   field_name varchar(55),
   field_value int
)
go

INSERT INTO [dbo].[custom_fields]
           ([ref_id]
           ,[field_id]
           ,[field_name]
           ,[field_value])          
    VALUES
 (1020, 1000,'width',10)
,(1020, 1001,'height',20)
,(1020, 1003,'thick',5)
,(2010, 1000,'width',15)
,(2010, 1001,'height',5)
,(2010, 1003,'thick',5)
,(2010, 1003,'units',5)
,(3000, 1000,'width',35)
,(3000, 1001,'height',55)
,(3000, 1003,'thick',20)

--returns all the records
select *
from custom_fields

After we create the table and insert the test data, we can run a query to select all the records. The results should as follows:




The problem with the way the data is stored is that those values are properties values for an entity associated by the ref_id, and we would want to read those values as columns instead of rows as shown below:


Pivot Query


To convert those rows to columns, we need to use the PIVOT relational operator which enables us to convert the table results into a different shape. Let’s write a SQL query that can do that for us.


SELECT ref_id,width,height,thick,units
FROM
(
  SELECT [ref_id], [field_name], [field_value]
  FROM custom_fields   
) fields
pivot
(
  MAX(field_value)
  FOR [field_name] in (width,height,thick,units)
) piv;



The syntax may not be clear at first, but the query pivots the data on the field_name (FOR Expression) values, so that they become the column heading. This matches the meta-data in the field_name column, so we can read the field value using the MAX function. 

When a field_name does not exist for a particular row, the return value is null.  For example, both ref_ids (1020, 3000) do not have a units custom field.

This should enable us to make more complex pivot queries using SQL Server.

Thanks for reading.

Originally published by ozkary.com