T-SQL Challenge - Combinations

Itzik provides a T-SQL challenge to compute all possible sums of combinations of elements.

Itzik Ben-Gan

January 22, 2008

1 Min Read
ITPro Today logo

You have a table called Vouchers where you store information about
vouchers, including voucher id (vid) and amount (amt). Run the following
code to create the Vouchers table and populate it with sample data:

SET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID('dbo.Vouchers') IS NOT NULL DROP TABLE dbo.Vouchers;GOCREATE TABLE dbo.Vouchers(  vid INT NOT NULL PRIMARY KEY,  amt MONEY NOT NULL);GOINSERT INTO dbo.Vouchers(vid, amt) VALUES(1, 500.0);INSERT INTO dbo.Vouchers(vid, amt) VALUES(2, 100.0);INSERT INTO dbo.Vouchers(vid, amt) VALUES(3, 30.0);INSERT INTO dbo.Vouchers(vid, amt) VALUES(4, 90.0);INSERT INTO dbo.Vouchers(vid, amt) VALUES(5, 40.0);INSERT INTO dbo.Vouchers(vid, amt) VALUES(6, 90.0);GO

Your task is to write a solution that returns all distinct sums of amounts that
can be produced by combining vouchers (one or more). The desired result
for the given sample data is (43 rows):

amt---------------------30.0040.0070.0090.00100.00120.00130.00140.00160.00170.00180.00190.00210.00220.00230.00250.00260.00280.00310.00320.00350.00500.00530.00540.00570.00590.00600.00620.00630.00640.00660.00670.00680.00690.00710.00720.00730.00750.00760.00780.00810.00820.00850.00

Cheers,
--
BG

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like