I was working with one of my project, I have to convert rows to columns and columns to rows from the SQL server .we can use PIVOT AND UNPIVOT in SQL server to convert rows to columns and columns to rows. I would like to share you guys how to use PIVOT AND UNPIVOT in real time scenario.
-- #############################################################################
-- 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
PIVOT TABLE:
-- #############################################################################
-- 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
select * from @cars
UNPIVOT TABLE:
