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