| Author |
Topic |
|
getsaby
Starting Member
11 Posts |
 Posted - 2006-08-31 : 12:08:59
|
| Is it possible to have MS SQL Query for We will have one large string, which will contain 1 row and n columns of data seperated by comma... |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-08-31 : 12:52:50
|
| depends on how you want to use the string. One simple way to display results that way is to go to:tools | options | results (tab) | change "Results output format" to CSVBe One with the OptimizerTG |
 |
|
|
getsaby
Starting Member
11 Posts |
Posted - 2006-08-31 : 13:11:37
|
| Well I want to do this using SQL only.Not by changing the display format and all.One string that will contain data from 1 row and n columns, seperated by comma or tab...so that i can count them laterthanks,saby |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-08-31 : 13:24:02
|
Well one brute force way would be to select a concatenation of all the values. You'd need to convert non-character based datatypes to varchar as well as deal with any nullable columns. ie:select myString = isNull([varcharCol1],'') + ',' + isNull(convert(varchar, [intCol2]),'') + ',' + isNull(convert(varchar, [datetimeCol3], 112),'') + etc... >>so that i can count them laterWhat is your big picture objective here? There may be a better way.Be One with the OptimizerTG |
|
|
|
getsaby
Starting Member
11 Posts |
Posted - 2006-08-31 : 13:31:02
|
| Hi thanks for the reply...I have this big problem....trying to break it up in smaller pices...heres the problem in detail that am facing...Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.The description of table is as, in this table all the fields are set to type varchar only, there are like 140 columns. This table is already generated, This table will b mine base table.Table: Dummy================================================== ==field1 field2 field3..........Empid Empname empaage.....1 sam 23...........2 rai 22...............n raj 45.............================================================== ==Now I want to create another table as "EMP" , with proper data typefields create table emp (empid int, empname varchar(100), empage int....)I don't want to have this hardcoded fields ...The table should look like as:Table: EMP================================================== ==Empid Empname empaage............1 sam 23...............2 rai 22...................n raj 45.................================================================== ==I want to do this dynamically.....Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]A small rough snippet/flow of the code will be appreciated....Waiting for replies........sabyEdit/Delete Message |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-08-31 : 13:57:03
|
| You didn't really state the problem you just started in with a solution. Are you importing data to sql server from an outside source like a vendor, customer, or a legacy system? If so, what form is the data in when you first get it? Or is your starting point really a sql table with 140 varchar columns? If so, you want to create a new table with the same column names but using proper datatypes? How do you know what the "proper" datatypes (and lengths and precisions etc) are for each column? Do you just guess based on the existing data? ugh. Do you need to do this for many different table structures? or the same table over and over again (or just one table one time)?Be One with the OptimizerTG |
|
|
|
getsaby
Starting Member
11 Posts |
Posted - 2006-08-31 : 14:26:25
|
| Hello:I have this data from a excel file, and using TSQL, I load this data into some temp. table, where all the fields are declare as varchars.That excel file itself is not in proper format. The real ata starts from row 7...The first seven row contains basic info of the file, which we will be stripping.7th row have column header info8th row onwards the actual data starts.So, for the new table, I will create fields using row 7, with proper data type...the task is to extract record from row 7 and use it, to construct this new table...where field1, field2 comes from row 7create table EMP ( @field1 varchar(), @field2 int.........), but this doesn't work...Yes, I think we have to specify the data type implicitly here..., but can the columns name be generated dynamically.I have like 4-5 excel file, with various column length, and i hv to do this for all of them.Files with 10-20 columns are OK, but there are some file which have 250 columns, It will become tedious to create table for so many columnsfield.I hope am making things clear here.Thanks,saby |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
getsaby
Starting Member
11 Posts |
Posted - 2006-08-31 : 15:37:32
|
| Yes....at first I taught of doing same...But, I guess you didn't got the gist of the problem.That EXCEL file is not in proper format.The first seven rows of cell contains junk data....info about the file like when it was created and all.....Actual data starts from row 8, which being the column header info.and at the bottom you have this "total" fields too...which we want to get rid off tooEven, if I follow the link logic that you gave, how can I skip the first 8th row from the excel and the last row, before it get dummped into that database table.I hope now you are getting the nature of the problem.The Excel file itself is not in proper format.Thanks,saby |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-31 : 16:19:44
|
| You should look at this article:Importing a Named Range From Microsoft Excelhttp://www.sqldts.com/?243CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-31 : 16:38:41
|
quote:
Originally posted by getsaby
Even, if I follow the link logic that you gave, how can I skip the first 8th row from the excel and the last row, before it get dumped into that database table.
That is explained in the link provided by Michael. Did you bother to read and learn?Use OPENROWSET with the first seven rows omitted. Then do a SELECT * FROM [Sheet1$A8:B93] WHERE f1 <> 'TOTAL'Peter LarssonHelsingborg, Sweden |
|
|
|
|
concatenate 1 row 'n' column into one string