question

Nick77 avatar image
Nick77 asked

Select many(>1000) columns from table

Hi I have a table with many columns (>3000). How can i select many(>1000) specific columns from this table? Any help would be appreciated. Thanks George
sql-server-2008selectcolumns
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Nick77, that is not an optimal design, what are you going to do when you need more dates? Add more columns? The maximum storage space for a row is 8060 bytes for non variable lenght columns. I would have one row per stock and date. And when I need to get the records I would get the stocks based on the date interval and use PIVOT to create the columns.
4 Likes 4 ·
how do you know what columns you want?
0 Likes 0 ·
I want to build a database with stock prices. The columns are the dates and the rows are the different stocks. So for example i want to obtain all the stocks from 1/1/2005 to 1/1/2007.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Do you mean you want assistance in generating the TSQL rather than having to type out the whole query? [SQL Prompt][1] from Red Gate has a column picker..... ![alt text][3] ---- Alternatively if you want to dynamically generate the sql, then try something along these lines ;with tally as ( select top 10000 ROW_NUMBER() over(order by sv1.number) N from master.dbo.spt_values sv1, master.dbo.spt_values sv2 ) , dates as ( select convert(varchar, cast('1 jan 2005' as datetime) + N, 103) as coldate from tally where cast('1 jan 2005' as datetime) + N between '1 jan 2005' and '1 jan 2007' ) select 'select ' + stuff((select '],['+coldate from dates for xml path('')),1,2,'') + '] from yourtable' Adjusting the date format to suit your column names [1]: http://www.red-gate.com/products/sql-development/sql-prompt/ [3]: /upfiles/sqlpromptcolumnpicker.PNG
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes that is what i mean.
0 Likes 0 ·
Thanks for help. but is there a more efficient way rathan than selecting 1000+ columns every time.
0 Likes 0 ·
@Nick77 : I've added a dynamic example to my answer above....
0 Likes 0 ·
Thank you very much..
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Ooops! Thats a lot of columns, are you using sparse columns? If you really need that many columns in your result, then Kevs recommendation is your best solution, but I would try to return as few columns as possible. Instead of having on query that returns everything, I would create more queries with fewer columns. To me it does sounds like you have a very denormalized database.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 yes I was avoiding the design 'issues'
0 Likes 0 ·
@Nick77 Wow, I have seen some questionable design (which other posters so politely avoided to discuss), but never something quite like this one. I have a DB of quotes at home, but the quotes table is designed such that it is clustered by quote date and company id (in this order). To get quotes for next day, I issue 5 calls to the service which returns info about 100 quotes per call and takes about 0.4 seconds to call the service, get results and insert data. This way it takes about 2 seconds to get all S&P 500 quotes for one day. If I ever need something like crosstabbed report displaying 1 ticker per record and as many days as I need then I can use a simple PIVOT. At the same time, getting the info from the data store for my charts and grids does not take time at all because the records are narrow, and a big bunch of narrow records is almost always better than a small bunch of the wide ones (I don't even want to go to the cumbersomeness of a necessity to add more columns and exceeding the 8060 limit which is evil but allowed in modern versions of SQL Server via in-row-overflows). I support wide tables design when it is a must (like for geochemists), but not just because :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.