September 17, 2012 at 9:23 am
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 amt2amt3 amt4 amt5
ABCD1234 44.92 14.422 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?
September 17, 2012 at 9:31 am
September 17, 2012 at 9:35 am
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 amt2amt3 amt4 amt5
ABCD1234 44.92 14.422 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 9:42 am
Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx
When you've read through that, read this excellent alternative by Dwain Camps[/url].
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
September 17, 2012 at 9:46 am
ChrisM@Work (9/17/2012)
Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspxWhen you've read through that, read this excellent alternative by Dwain Camps[/url].
But if you are really using SQL 2005, you're going to have to replace the values with the select ... union all select syntax.
September 17, 2012 at 9:57 am
roryp 96873 (9/17/2012)
ChrisM@Work (9/17/2012)
Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspxWhen you've read through that, read this excellent alternative by Dwain Camps[/url].
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 😀
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
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
September 17, 2012 at 10:00 am
--====== 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);
September 17, 2012 at 10:02 am
ChrisM@Work (9/17/2012)
Thanks Rory 😀
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:
September 17, 2012 at 10:58 am
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.
September 17, 2012 at 10:08 pm
ChrisM@Work (9/17/2012)
Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspxWhen you've read through that, read this excellent alternative by Dwain Camps[/url].
I wouldn't exactly call it mine (:blush:) 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply