SQL Puzzle 8

Published Tuesday, November 27, 2007 7:12 PM


/*
So I thought it was just about time for another puzzle but I was having great difficulty in coming up with a new challenge. Luckily Andras has posted a blog entry about how the
POWERSUM function has been removed from SQL Server.

http://www.simple-talk.com/community/blogs/andras/archive/2007/11/22/40021.aspx

This puzzle is a nice simple one and is to write a single select statement that will return the same results as POWERSUM when run on the supplied table variable. The input values will be between 0 and 127 but it would be nice to see solutions that will scale to beyond that. Andras has posted an explanation of what POWERSUM does but if it is still unclear exactly what it does (The output seems to be the reverse of what I expect it to be!) add a comment to the blog and I will add some clarification. Have fun!

Lionel

*/

SET NOCOUNT ON

DECLARE @Data TABLE
(
Col INT
)

INSERT INTO @Data(col)
SELECT 1
INSERT INTO @Data(col)
SELECT 1
INSERT INTO @Data(col)
SELECT 2
INSERT INTO @Data(col)
SELECT 98
INSERT INTO @Data(col)
SELECT 31
INSERT INTO @Data(col)
SELECT 32
INSERT INTO @Data(col)
SELECT 32
INSERT INTO @Data(col)
SELECT 63
INSERT INTO @Data(col)
SELECT 17
INSERT INTO @Data(col)
SELECT 100
INSERT INTO @Data(col)
SELECT 56
INSERT INTO @Data(col)
SELECT 24
INSERT INTO @Data(col)
SELECT 17
INSERT INTO @Data(col)
SELECT 2
INSERT INTO @Data(col)
SELECT 127
INSERT INTO @Data(col)
SELECT 76
INSERT INTO @Data(col)
SELECT 84
INSERT INTO @Data(col)
SELECT 99
INSERT INTO @Data(col)
SELECT 12
INSERT INTO @Data(col)
SELECT 103
SET NOCOUNT OFF

-- This should return the same as
-- SELECT POWERSUM(col) FROM @Data
-- which is 0x0610028101000081001010009C000080