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
@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.
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
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.
@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 :)
