Inserting A Record For Each Separate Aggregate (solved)

Hi,

As I wrote my message the solution came to me, so I thought I would
post anyway for others to see in case it was useful:

Here is some sample DDL for this question:

CREATE TABLE Source (
my_value INT NOT NULL )
GO
INSERT INTO Source VALUES (1)
INSERT INTO Source VALUES (2)
INSERT INTO Source VALUES (3)
GO
CREATE TABLE Destination (
value_type VARCHAR(10) NOT NULL,
value INT )
GO

I would like to fill the destination with a row for the COUNT, SUM,
MIN, and MAX. My own problem is of course much more complex than this,
but this is the basic stumbling block for me now. So, the rows that I
would expect to see in Destination are:

value_type value
---------- -----
COUNT 3
SUM 6
MIN 1
MAX 3

The solution that I came up with was to add a Value_Types table:

CREATE TABLE Value_Types (
value_type VARCHAR(10) NOT NULL )
GO
INSERT INTO Value_Types VALUES ('COUNT')
INSERT INTO Value_Types VALUES ('SUM')
INSERT INTO Value_Types VALUES ('MAX')
INSERT INTO Value_Types VALUES ('MIN')
GO

Now the SQL is pretty simple:

SELECT V.value_type,
CASE V.value_type
WHEN 'COUNT' THEN COUNT(*)
WHEN 'SUM' THEN SUM(S.my_value)
WHEN 'MAX' THEN MAX(S.my_value)
WHEN 'MIN' THEN MIN(S.my_value)
END
FROM Source S
INNER JOIN Value_Types V ON 1=1

-Tom.

P.S. - I know that I did not include primary or foreign keys in my DDL.
I'll leave it as an excercise to the reader to figure those out. I
think the code adequately explains the concept.