June 24, 2012 at 9:43 pm
The result set for this is:
MyId MinAmt
Ali 196
Jim 0
Joe 1
Stu 199
If I wanted to say ignore amounts under 150, the result set would look like this:
MyId MinAmt
Ali 196
Jim 197
Joe 198
Stu 199
Any ideas how to add a where cluse to this?
CREATE TABLE #test
(MyId varchar(10), MV1 int, MV2 int, MV3 int, MV4 int)
INSERT INTO #test
(MyId, MV1, MV2, MV3, MV4)
SELECT 'Ali', 198, 250, 300, 196 UNION ALL
SELECT 'Jim', 200, 0, 197, 400 UNION ALL
SELECT 'Joe', 200, 1, 300, 198 UNION ALL
SELECT 'Stu', 200, 199, 300, 400
SELECT MyId, MIN(newValues) as MinAmt FROM(
SELECT MyId, MV1, MV2, MV3, MV4
FROM #test) P
UNPIVOT
(newValues FOR MyCalc IN (MV1, MV2, MV3, MV4))
AS UNPVT
GROUP BY MyId
June 24, 2012 at 10:00 pm
This?
SELECT MyId ,MinAmt = MIN( Pivot_Handle.Vals )
FROM #test T
UNPIVOT ( Vals FOR Cols IN (MV1 , MV2, MV3, MV4)) Pivot_Handle
WHERE Pivot_Handle.Vals >= 150
GROUP BY MYID
June 24, 2012 at 10:07 pm
Yes, perfect. Thanks!
June 25, 2012 at 12:04 am
You can also do it like this:
SELECT x.MyId, MIN(MinAmt)
FROM #test
CROSS APPLY (
VALUES (MyID, MV1), (MyID, MV2), (MyID, MV3), (MyID, MV4)) x(MyID, MinAmt)
WHERE MinAmt >= 150
GROUP BY x.MyID
I've run some performance tests using this method against UNPIVOT and I've found that it is about 15% cheaper on CPU and about 3% faster on elapsed time (on average).
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
June 26, 2012 at 6:25 am
dwain.c (6/25/2012)
You can also do it like this:
SELECT x.MyId, MIN(MinAmt)
FROM #test
CROSS APPLY (
VALUES (MyID, MV1), (MyID, MV2), (MyID, MV3), (MyID, MV4)) x(MyID, MinAmt)
WHERE MinAmt >= 150
GROUP BY x.MyID
I've run some performance tests using this method against UNPIVOT and I've found that it is about 15% cheaper on CPU and about 3% faster on elapsed time (on average).
I've never seen this syntax before. Can you explain what's happening? In particular, how does the CROSS APPLY know to use values from #test?
Thanks
June 26, 2012 at 6:22 pm
Tom Bakerman (6/26/2012)
dwain.c (6/25/2012)
You can also do it like this:
SELECT x.MyId, MIN(MinAmt)
FROM #test
CROSS APPLY (
VALUES (MyID, MV1), (MyID, MV2), (MyID, MV3), (MyID, MV4)) x(MyID, MinAmt)
WHERE MinAmt >= 150
GROUP BY x.MyID
I've run some performance tests using this method against UNPIVOT and I've found that it is about 15% cheaper on CPU and about 3% faster on elapsed time (on average).
I've never seen this syntax before. Can you explain what's happening? In particular, how does the CROSS APPLY know to use values from #test?
Thanks
As with any CROSS APPLY, you can reference columns in the left table. I just happened across this technique while playing with VALUES sets and I don't think it's widely known, although there are a few articles that mention it if you Google "CROSS APPLY VALUES UNPIVOT." Here are two.
http://bradsruminations.blogspot.com/2010/02/spotlight-on-unpivot-part-1.html
http://www.sqlmag.com/article/database-administration/unpivoting-data
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply