Convert Rows to Columns, Columns to Rows in SQL Server using PIVOT and UNPIVOT

Filed under: SQL sqlserver pivot unpivot

A common question still seen in SQL Server forums is how does one convert the result set from rows to columns or columns to rows.  The PIVOT and UNPIVOT operators can be used to perform this task.

Included below are two examples.  A few important things to note are:

  1. PIVOT requires an aggregate function so be careful if the pivot does not occur on a unique value.
  2. Both PIVOT and UNPIVOT require a hard-coded list of columns/values to pivot/unpivot.
  3. The alias assigned to the pivot/unpivot is used in the select clause.

Anyway, here are the examples:

-- #############################################################################
-- Using PIVOT to Convert Rows to Columns

-- First, declare a sample table of cars.
declare @cars as table (
 car_id tinyint,
 attribute varchar(20),
 value varchar(20)
)

-- Then, populate that table with some values.
-- Two example cars are being inserted each with its Make, Model, and Color.
insert into @cars(car_id, attribute, value)
values		(1, 'Make', 'VW'),
			(1, 'Model', 'Rabbit'),
			(1, 'Color', 'Gold'),
			(2, 'Make', 'Jeep'),
			(2, 'Model', 'Wrangler'),
			(2, 'Color', 'Gray')


-- Now, PIVOT is used to transform this data from rows to columns.
-- Notice that PIVOT requires an aggregate so be cautious if you
-- are pivoting a result set without using a unique value.
-- Note that hard-coding the list of attributes to pivot is required.
select pvt.car_id, pvt.Make, pvt.Model, pvt.Color
from @cars c
pivot (
 min(value)
 for attribute in ([Make],[Model],[Color])
) pvt


GO

-- #############################################################################
-- Using UNPIVOT to Convert Columns to Rows

-- First, declare a sample table of cars.
declare @cars as table (
 car_id tinyint identity(1,1),
 make varchar(20),
 model varchar(20),
 color varchar(20)
)

-- Then, populate that table with some values.
-- Two example cars are being inserted each with its Make, Model, and Color.
insert into @cars(make, model, color)
values		('VW', 'Rabbit', 'Gold'),
			('Jeep', 'Wrangler', 'Gray')


-- Now, UNPIVOT is used to swap our columns into rows.
-- Hard-coding the list of columns is necessary as seen with PIVOT.
select unpvt.car_id, unpvt.attribute, unpvt.value
from @cars c
unpivot ( 
 value 
 for attribute in (Make, Model, Color)
) unpvt

Result sets:

Questions?

Send me an email: jontav@yahoo.com

Share