For those of us who spend time querying Microsoft SQL Server, each new version of the product gives us new functionality that can be as helpful as a new belt full of tools to a carpenter. One tool (first available in SQL 2008) that has proved useful in our ad hoc queries is the expanded VALUES clause. I use it in three primary ways:
- A more intuitive way to insert data into a table.
- A great technique for quick and dirty derived tables.
- A way to aggregate data over columns rather than rows.
Some SQL statements that spell out the details of those three techniques follow. The first two are fairly easy to understand by simply running the SQL code. The third technique is a bit more involved: for more explanation and code samples, refer to this article in SQL Server Magazine by Itzik Ben-Gan.
-- declare a table variable for use below declare @TableVar table ( TermId int, LicenseId int, MaleCount int, FemaleCount int, [Description] varchar(500) ) -- first use of the values clause: one insert statement inserting multiple rows. insert into @TableVar values (20083, 1, 12, 15, 'Sample data - bogus'), (20093, 4, 31, 47, 'Sample data - bogus'), (20103, 3, 50, 22, 'Sample data - bogus'), (20113, 2, 67, 83, 'Sample data - bogus'), (20123, 4, 39, 58, 'Sample data - bogus') -- second use of values clause: inline, quick & dirty derived table select t.TermId, t.LicenseId, t.MaleCount, t.FemaleCount, tmp.* from ( values -- use values to create derived table (1, '130301-N','Science Chemistry 9-12 + 5-8 General Science'), (2, '130302-N', 'Science Physics 9-12 + 5-8 General Science'), (3, '130400-N', 'Science Earth 9-12 + 5-8 General Science'), (4, '130600', 'Science General 5-8') ) as tmp (LicenseId, LicenseCode, LicenseDescription) -- name columns join @TableVar t on tmp.LicenseId = t.LicenseId -- third use of values clause: aggregate function over columns -- rather than rows. Better performance than unpivot with large sets. select TermId, ( select max(mycol) from (values ('FemaleCount', FemaleCount), ('MaleCount', MaleCount)) as mytable(mylabel, mycol) ) maximum_value from @TableVar -- compare syntax above to case statement select TermId, case when FemaleCount > MaleCount then FemaleCount else MaleCount end maximum_value from @TableVar -- now compare to unpivot select TermId, max(col) as maximum_value from ( select TermId, col from @TableVar unpivot (col for cols in (FemaleCount, MaleCount)) as unpivott ) p group by TermId