Help with "Create View" statement and Eorror Message

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.As much information vertically, not horizontally.

Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

Why are you doing this? Maybe somebody here can find a better approach for you to take.|||No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

-PatP|||I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.|||You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.|||Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns. :D|||I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
How am I going to update number of views on daily basis? :confused:|||All kidding aside.

Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!|||This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

Sorry about the confusion. And thank you for the help.|||Good Morning All,
Hope you all had a great weekend!
I think I am probably asking a silly question but...
I created a view by using the following statement:

create view TestView1
as
select date as Date,
XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
from MyTable
Group by Date

The structure of MyTable is:
Date(datetime) ID(char 10) Field1(float)
1/1/65 XXXXX -999.999
1/4/65 XXXXX -999.999
...
2/24/05 XXXXX 500
2/25/05 XXXXX 550
1/1/65 YYYYY -999.999
1/4/65 YYYYY -999.999
...
2/24/05 YYYYY 600
2/25/05 YYYYY 650

when I run "select * from TestView order by date"
The actual results I got:
Date XXXXX YYYYY
1/1/65 0.0 0.0
1/4/65 0.0 0.0
...
2/24/05 500 600
2/25/05 550 650

This is the results I should expect:
Date XXXXX YYYYY
1/1/65 -999.999 -999.999
1/4/65 -999.999 -999.999
...
2/24/05 500 600
2/25/05 550 650

What is wrong with my create view statement? Do I have to specify the datatype?

Thank you for the help in advance.|||What is wrong with it?

The question is, "What good is it?"

What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

What are you planning to do with this?|||This is going to be the data source for another application (Matlab).
And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

Should I put information into a table instead of View?

Thank you for the help!|||MatLab can't accept normalized data?

And it can't do its own crosstabs?

That is pretty weak.

I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.|||Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
If you think the only possible solution should be on Matlab side, I guess I have to work on that.
However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

Any suggestion and comments are truely appreciated!
shiparsons|||I don't have that problem..you probably shouldn't be using float though...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float)
GO

INSERT INTO myTable99([Date],[ID],Field1)
SELECT '1/1/65', 'XXXXX', -999.999 UNION ALL
SELECT '1/4/65', 'XXXXX', -999.999 UNION ALL
SELECT '2/24/05', 'XXXXX', 500 UNION ALL
SELECT '2/25/05', 'XXXXX', 550 UNION ALL
SELECT '1/1/65', 'YYYYY', -999.999 UNION ALL
SELECT '1/4/65', 'YYYYY', -999.999 UNION ALL
SELECT '2/24/05', 'YYYYY', 600 UNION ALL
SELECT '2/25/05', 'YYYYY', 650
GO

CREATE VIEW myView99
AS
SELECT [Date]
, SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
, SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
FROM MyTable99
GROUP BY [Date]
GO

SELECT * FROM myView99
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO|||Brett,
Thank you for the help! You are right. It works fine.
The problem was on my end. In my statement I had a space was quoted in for ID field. (ID=' XXXXX ' instead of ID='XXXXX')

:p