in my database table , i have one field (say field1) which contains the value like 1,2,3,10,11,20, a, b,c , 30, 40. I want to write a query which sore the result on the base of field1 and output should be like
whether it is possible through query or i need to adjust in code ?
Yhat should be possible using a query. Add a computed field that indicates whether it's a number or a range of characters, and put that as the first column to sort on. Or check out the collations if you're on Sql Server - might be as easy as changing the setting and have the server order everything in the correct way for your locale.
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names.
It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
None I know about, but you can select a range from the sheet like: SELECT * FROM [Sheet1$A4:F1243] Which could solve your problem.
But you would need to know, or find out, how many rows there are before doing the select.
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget).
Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g. ['sheet 1$'] and I have been unable to get that to work with a range.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''. System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.
Just for testing I renamed the sheet to remove the SPACE and it works.
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format.
The UserStatistics table has a foreign key to the User table (user_id in UserStatistics)
Statistics might be height, weight, etc...
The table should look like this:
Name | Height | Weight
Joe 6' 150
And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?