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:

CodeProject

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