SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unpivot Help


Unpivot Help

Author
Message
rburko
rburko
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 365
This should be easy but I'm not getting this to work.

I have a number of rows that look something like this:

reckey amt1 amt2 amt3 amt4 amt5
ABCD1234 44.92 14.4 22 0 11.52



I'm trying to use the t-sql Unpiovt statement to create this:


reckey amtnumber amt
ABCD1234 1 44.92
ABCD1234 2 14.4
ABCD1234 3 22
ABCD1234 4 0
ABCD1234 5 11.52





Can anyone provide an example of how to make this work?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52328 Visits: 38684
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33969 Visits: 17681
rburko (9/17/2012)
This should be easy but I'm not getting this to work.

I have a number of rows that look something like this:

reckey amt1 amt2 amt3 amt4 amt5
ABCD1234 44.92 14.4 22 0 11.52



I'm trying to use the t-sql Unpiovt statement to create this:


reckey amtnumber amt
ABCD1234 1 44.92
ABCD1234 2 14.4
ABCD1234 3 22
ABCD1234 4 0
ABCD1234 5 11.52





Can anyone provide an example of how to make this work?


Other than starting with the link provided by Lynn I would advise you to normalize your data if at all possible. This type of structure is a PITA to work with.

If you can post ddl, sample data and what you tried so far you will likely find a lot of help. Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ChrisM@Work
ChrisM@Work
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22219 Visits: 19709


When you've read through that, read this excellent alternative by Dwain Camps.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
roryp 96873
roryp 96873
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 Visits: 6605
ChrisM@Work (9/17/2012)


When you've read through that, read this excellent alternative by Dwain Camps.


But if you are really using SQL 2005, you're going to have to replace the values with the select ... union all select syntax.
ChrisM@Work
ChrisM@Work
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22219 Visits: 19709
roryp 96873 (9/17/2012)
ChrisM@Work (9/17/2012)


When you've read through that, read this excellent alternative by Dwain Camps.


But if you are really using SQL 2005, you're going to have to replace the values with the select ... union all select syntax.


Thanks Rory :-D

SELECT reckey, ProductName, ProductQty
FROM (SELECT reckey = 'ABCD1234', amt1 = 44.92, amt2 = 14.4, amt3 = 22, amt4 = 0, amt5 = 11.52) d
CROSS APPLY (
VALUES
('amt1', amt1),
('amt2', amt2),
('amt3', amt3),
('amt4', amt4),
('amt5', amt5)
) x (ProductName, ProductQty)
WHERE ProductQty IS NOT NULL

SELECT reckey, ProductName, ProductQty
FROM (SELECT reckey = 'ABCD1234', amt1 = 44.92, amt2 = 14.4, amt3 = 22, amt4 = 0, amt5 = 11.52) d
CROSS APPLY (
SELECT 'amt1', amt1 UNION ALL
SELECT 'amt2', amt2 UNION ALL
SELECT 'amt3', amt3 UNION ALL
SELECT 'amt4', amt4 UNION ALL
SELECT 'amt5', amt5
) x (ProductName, ProductQty)
WHERE ProductQty IS NOT NULL




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
laurie-789651
laurie-789651
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 1272


--====== TEST DATA SET-UP =================
if object_id('dbo.pvt') is not null
drop table dbo.pvt;

create table dbo.pvt
(
reckey varchar(10),
amt1 decimal(8,2),
amt2 decimal(8,2),
amt3 decimal(8,2),
amt4 decimal(8,2),
amt5 decimal(8,2)
);

insert dbo.pvt values ( 'ABCD1234', 44.92, 14.4, 22, 0, 11.52 );
insert dbo.pvt values ( 'ABCD5678', 3.12, 17.7, 0, 13, 100.5 );

/*
I'm trying to use the t-sql Unpivot statement to create this:

reckey amtnumber amt
ABCD1234 1 44.92
ABCD1234 2 14.4
ABCD1234 3 22
ABCD1234 4 0
ABCD1234 5 11.52
*/

--====== SOLUTIONS =================

-- SQL 2005: Unpivot the table.
SELECT reckey, amtnumber, amt
FROM
(SELECT reckey, amt1, amt2, amt3, amt4, amt5
FROM pvt) p
UNPIVOT
(amt FOR amtnumber IN
(amt1, amt2, amt3, amt4, amt5)
)AS unpvt;

-- SQL 2008: You can use Cross Apply:
SELECT reckey, amtnumber, amt
FROM dbo.pvt
CROSS APPLY (
VALUES (1, amt1)
,(2, amt2)
,(3, amt3)
,(4, amt4)
,(5, amt5)
) a(amtnumber, amt);




roryp 96873
roryp 96873
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 Visits: 6605
ChrisM@Work (9/17/2012)

Thanks Rory :-D


No problem. I just ran into this problem a week or so ago and after spending way too long searching for a typo in my code, I finally realized I was on a 2k5 box so it's pretty fresh in my memory. Hehe
rburko
rburko
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 365
Thanks for all the replys!

Lynn, I read the Microsoft documentation but I thought the documentation for pivot was better than it was for Unpivot. If you know of better examples, please let me know.

Chris, thanks for the link using cross apply.

Laurie, that's just what I was looking for! I only made a small change for my purposes:


SELECT reckey, amtnumber, amt
FROM
(SELECT reckey, amt1 as [1], amt2 as [2], amt3 as [3], amt4 as [4], amt5 as [5]
FROM pvt) p
UNPIVOT
(amt FOR amtnumber IN
([1], [2], [3], [4], [5])
)AS unpvt;



Sad to say, I had something similar but I reversed some things (e.g. [1] as amt1, etc.). Thanks for setting me straight.
dwain.c
dwain.c
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9743 Visits: 6431
ChrisM@Work (9/17/2012)


When you've read through that, read this excellent alternative by Dwain Camps.


I wouldn't exactly call it mine (:blushSmile as the initial links in the article attribute it to Brad Shulz and Itzik Ben-Gan (and there are probably others that have written about it before me).

But thanks for thinking of me.:-D


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search