Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unpivot Help Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, 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?
Post #1360278
Posted Monday, September 17, 2012 9:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,705, Visits: 32,358
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx


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)
Post #1360282
Posted Monday, September 17, 2012 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:08 PM
Points: 13,008, Visits: 12,429
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 Moden's 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)
Post #1360286
Posted Monday, September 17, 2012 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 6,751, Visits: 13,899


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
Post #1360289
Posted Monday, September 17, 2012 9:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 720, Visits: 6,088
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.
Post #1360293
Posted Monday, September 17, 2012 9:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 6,751, Visits: 13,899
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

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
Post #1360301
Posted Monday, September 17, 2012 10:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079

--====== 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);



Post #1360303
Posted Monday, September 17, 2012 10:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 720, Visits: 6,088
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.
Post #1360304
Posted Monday, September 17, 2012 10:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, 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.
Post #1360344
Posted Monday, September 17, 2012 10:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
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 () 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.



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!
Post #1360543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse