Help with Pivot (or Unpivot)
-
I originally posted this a couple of weeks ago, and (very unusually) got no replies. It has 'Excel' in the title which may have put some people off. , however it is not specifically an Excel issue, as the data is imported to SQL Server. Its just a rather complex pivot/unpivot.
See http://www.sqlservercentral.com/Forums/Topic1495433-391-1.aspx?Update=1
Any help appreciated before I go and write some possibly unnecessary CLR thing
-
SELECT t.[AGE],ca.[Rate],ca.[Amount]
FROM [dbo].[Sheet1$] t
CROSS APPLY (VALUES([2#00%], [2#25%]), ([2#50%], [2#75%]), ([3#00%], [3#25%]), ([3#50%], [3#75%])) ca([Rate],[Amount])
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Many thanks. I knew there must be a simple solution. 😀
* Note to self: Must learn about CROSS APPLY *
-
Have a look at Dwains excellent article
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Just to be complete, the actual solution is as follows (slightly different from Mark's solution)
SELECT t.[AGE],ca.[Rate],ca.[Amount]
FROM [dbo].[Sheet1$] t
CROSS APPLY (VALUES(2.00,[2#00%]),(2.25, [2#25%]), (2.50, [2#50%]),(2.75, [2#75%]), (3.00, [3#00%]),(3.25, [3#25%]), (3.50, [3#50%]),(3.75, [3#75%])) ca([Rate],[Amount])
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply