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

INSERT INTO [dbo].[custom_fields]
 (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
  SELECT [ref_id], [field_name], [field_value]
  FROM custom_fields   
) fields
  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


Post a Comment