Unpivot

Question

Post reply

Unpivot

polkadot

SSChampion

Points: 13664
More actions
April 22, 2016 at 3:29 pm

#306832

Hi, I need the column name being unpivoted to appear in result set, like this
select 1, 'Car', 1, 'FirstRate', 125
union
select 1, 'Car', 2, 'SecondRate', 234
union
select 1, 'Car', 3, 'ThirdRate', 352
My unpivot statement
SELECT Id,
Product,
ROW_NUMBER()OVER(Partition By Id Order By RentalRates) as RowId,
RentalRate
FROM
(
SELECT Id, Product, FirstRate, SecondRate, ThirdRate
FROM RentalRates
) Main
UNPIVOT
(
RentalRate FOR RentalRates IN (FirstRate, SecondRate, ThirdRate )
) Rate
My unpivot results are like this:
select 1, 'Car', 1, 125
union
select 1, 'Car', 2, 234
union
select 1, 'Car', 3, 352
DDL for sample table and data
-- Create sample table
CREATE TABLE RentalRates
(Id INT,
Product VARCHAR(500),
FirstRate int,
SecondRate int,
ThirdRate int
)
GO
-- Load Sample data
INSERT INTO RentalRates SELECT
1, 'Car', 125, 234, 352
UNION ALL SELECT
2, 'Bike', 333, 467, 497
UNION ALL SELECT
3, 'Cycle', 501, 604,587
GO
--Quote me

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

select id

,product

,rentalrates

,row_number() over (partition by id order by RentalRates) as RowId

,RentalRate

from (select id

,product

,FirstRate

,SecondRate

,ThirdRate

from RentalRates

) Main

unpivot

(

RentalRate for RentalRates in (FirstRate, SecondRate, ThirdRate)

) Rate

polkadot SSChampion Points: 13664 More actions · Answer 2

thanks for the quick help. Never would have thought like this.

--Quote me

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Another way to do unpivots is with CROSS APPLY.

I generally prefer doing it that way.

Here's the equivalent query in that form:

SELECT ID,

Product,

RentalRates,

RowId,

RentalRate

FROM RentalRates

CROSS APPLY

(

SELECT RowId=1, RentalRates='FirstRate', RentalRate=FirstRate

UNION ALL

SELECT RowId=2, RentalRates='SecondRate', RentalRate=SecondRate

UNION ALL

SELECT RowId=3, RentalRates='ThirdRate', RentalRate=ThirdRate

) AS cross_apply_unpivot;

Cheers!

polkadot SSChampion Points: 13664 More actions · Answer 4

Thanks for showing there's an alternative. I've got 9+ columns and will try to modify away from unpivot function. Have 20,000 rows. Suppose using CROSS APPLY will as a rule perform faster?

--Quote me

pietlinden SSC Guru Points: 63503 More actions · Answer 5

You might find this article helpful:

Unpivot a Table Using Cross Apply[/url]

I'm pretty sure I asked a question like this a while back, and the trick is to use dynamic SQL to grab a list of column names to unpivot, so that you get groups like this:

CROSS APPLY (VALUES (Question1, Answer1),

(Question2, Answer2),

(Question3, Answer3),

(Question4, Answer4),

(Question5, Answer5))

CrossApplied (Question, Answer)

After searching for a second, found this by Aaron Bertrand: Use SQL Server's UNPIVOT operator to dynamically normalize output[/url]