"How can I do this so that ... I don't have to add another
Min(Case) As DateTypeNew statement?"

You can't, at least not reasonably.

What you have specified is a cross-tabulation view, and SQL has no native
support for such a beast.

You could probably concoct a stored procedure to build some dynamic SQL, and
then execute it, based on column data in the INFORMATION_SCHEMA metadata
views, but I really wouldn't recommend that approach, as dynamic SQL is slow
and can be a serious security compromise.

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Darrel Keeler [mailto:keeler_d@s...]
Sent: Monday, February 10, 2003 12:54 PM
To: sql language
Subject: [sql_language] Help with View


I am using SQL server 2000 and am trying to create a horizontal view from a
table.  The simplified Date table has columns ID, DateType, DateValue.  I
want to create a horizontal view that would have columns ID, DateType1,
DateType2, DateType3, ...  - so an example record would be 1234, 1/01/2001,
2/02/2002, 3/03/2003, ...  This would allow me to see all the dates
associated to an ID in one record.

I have been able to do this but statically as:

SELECT     DateTestMain.ID,
MIN(CASE DateTest.DateCode WHEN 'a' THEN TheDate ELSE NULL END) AS
DateType1,
MIN(CASE DateTest.DateCode WHEN 'b' THEN TheDate ELSE NULL END) AS
DateType2,
...
FROM         DateTest RIGHT OUTER JOIN
                      DateTestMain ON DateTest.ID = DateTestMain.ID
GROUP BY DateTestMain.ID

However, my question is:  How can I do this so that if I add a new date type
it will automatically go into my view and I don't have to add another
Min(Case) As DateTypeNew statement?

Thanks,
     --Darrel