Using APPLY To Aggregate Unpivoted Data

Dan Clemens gives us yet another practical use of the APPLY operator:

I had a situation last week where I needed to find the MIN() and MAX() values of some data. Normally this would be pretty straightforward, but the problem was I needed to identify the MIN() and MAX() values amongst multiple columns. So today I bring you a SQL tip using APPLY.

Looking at sys.procedures we can see I have a create_date and a modify_date column for each record. What if we needed to return the MIN() or MAX() value from those 2 (or more) columns?

SELECT [Name]
,create_date
,modify_date
FROM sys.procedures AS p
WHERE p.[name] = 'ChangePassword';

In this two-column example, it’s not too difficult.  As you add more and more columns, the solution remains the same, though the urge to ask why all of these dates are unpivoted might increase…

Related Posts

Thoughts on Certification

Eugene Meidinger is certifiable: This being a complex topic, I thought I’d lay out the various factors to give a more comprehensive answer than you can easily fit in a tweet. So the first two questions we need to answer are “Why do certs exist?” and “Why do people take them?”. Without these, we can’t […]

Read More

Disable Lightweight Pooling

Randolph West explains why enabling lightweight pooling in SQL Server is almost always a bad idea: When can I enable lightweight pooling then?Don’t. But if you must, these are the conditions under which Microsoft suggests it may be useful:– Large multi-processor servers are in use.– All servers are running at or near maximum capacity.– A lot […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031