Hi,
how to:
using dts convert row in table to column.
i have table:
col1,col2
b1 , 1
b2 , 2
b1 , 4
b3 , 3
b2 , 5
and i want using dts convert/rewrite this table to another table:
b1,b2,b3
1,null,null
null,2,null
4,null,null
null,null,3
null,5,null
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies View RelatedHi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
I’ve a vertical table as follows: CMDATA
IDFormIDRecordCountFieldNameValue
15251204 Name John
25881204 OrganizationGE
35251257 Name Peter
45881257 OrganizationIntel
55251272 Name Rita
And a horizontal table as follows: CMFORM
IDFormIDRecordCount
15251204
25251257
35251272
45881204
55881257
Where
CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.RecordCount
Now I want select records as follows:
RecordCountNameOrganization
1204 JohnGE
1257 Peter Intel
1272 RitaNULL
I’ve written following query for that but it works properly only if all the record count has similar fieldnames.
SELECTCMCF.RecordCount,
CMD1.VALUE AS Name,
CMD2.VALUE AS Organization
FROMCMDATA CMD1
JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCOUNT = CMCF.RECORDCOUNT
JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCOUNT = CMCF.RECORDCOUNT
WHERECMD1.FIELDNAME = 'Name'
AND CMD2.FIELDNAME = 'Organization'
AND CMCF.RecordCount in(1204,1257,1272)
Is there any other way to handle this?
I’ve a vertical table as follows: CMDATA
IDFormIDRecordCountFieldNameValue
15251204 Name John
25881204 Organization GE
35251257 Name Peter
45881257 OrganizationIntel
55251272 Name Rita
And a horizontal table as follows: CMFORM
IDFormIDRecordCount
15251204
25251257
35251272
45881204
55881257
Where
CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.Record Count
Now I want select records as follows:
RecordCountNameOrganization
1204 JohnGE
1257 PeterIntel
1272 RitaNULL
I’ve written following query for that but it works properly only if all the record count has similar fieldnames.
SELECTCMCF.RecordCount,
CMD1.VALUE AS Name,
CMD2.VALUE AS Organization
FROMCMDATA CMD1
JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCount = CMCF.RECORDCount
JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCount = CMCF.RECORDCount
WHERECMD1.FIELDNAME = 'Name'
AND CMD2.FIELDNAME = 'Organization'
AND CMCF.RecordCount in(1204,1257,1272)
Is there any other way to handle this?
Pls advise how to "convert" a record from table A into table B (to perform similar "transpose" function in excel)
e.g a record in Table A with a key column & a number of data columns.
key: 1
col_1:A
col_2:B
col_3:C
...
col_26:Z
become in Table B with a key column & table A column name & data value.
key col value
1 col_1 A
1 col_2 B
...
1 col_26 Z
thx
I have the following table:
RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8
etc....
I need to transform it to this:
1 2 3
Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8
Can this be done in SQL?
Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.
suppose table is like this. username parentid downline
B A left
C A right
D B left
E B right....
I have to also determine the level in the tree....please help...
Hi,
I've got about 6000 tables in foxpro 3.0 and a database in sql srv 2000.
now i've to convert the data of these 6000 tables into the database. the problem is that i have to convert all the tables seperatly!
is there a tool where i can convert these tables automatically?
i know it can with DTS, but the 6000 tables are divided over 1400 directories. So with DTS i can't convert them automatically.
does anyone know a solution?
tblname rowno colname colvalue
emp 1 Title Mr
emp 1 firstname XYZ
emp 1 lastname M
I want to get as
title first_name lastname
Mr XYZ M
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
I am using vs2008 with c#.I have table in my database that has email addresses stored in a column. I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task.
View 3 Replies View RelatedI have a flat table from 1 of the clients with a lot of fields (more then 100) like this
Item F1 F2 F3 .... F(N)
---------------------------------------------
100 X X
101 X
There are more 10000 records , X is the data inside the field.
I need to quickly convert it to this table
Item FieldNumber Value
----------------------------
100 1 X
100 2 X
101 1 X
Any ideas ?
Thanks
Mikhail
Hi everyone,I have some data in a CSV file, and I have to import it into a table. For some reason, I am supposed to import this data into a temp table and then move it to the original table and I have to convert it to the right data types while I do this. Is there a better way to do this and how can I give custom error messages saying, for e.g., the data type cannot be converted, the right number of records are not present etc.
Thanks for the help.
Hello,
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
Thanks all in advance,
Saurav
Hi
I would like to convert sp_who into a table/query. Please help
Thanks
I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?
View 4 Replies View RelatedHi
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Thanks in advance
Hi
How to convert a sp_who in into a table/query
Please help
Raj.
I have imported some data to sql2k from my old system. Somehow, it importedinvoice amount to char type.I just created another column called invamt2 type NUMERIC so I can copy orconvert content of invamt which is type CHAR. There are about 50,000records.How can I convert/cast from char type to numeric type ?Thanks
View 2 Replies View RelatedHi all!
I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?
Thanks.
i have 12 different sql server tables. all are binded to one gridview. i have kept one admin user to view the details of the 12 tables in a gridview (pagesize=5). now the admin wants to generate 12 CSV files from the 12 tables (that is, he has to choose the table name from a dropdown and then has to click "Generate" button. CSV file name should be like this "<table_name>_<date>" ). also want to put links(dynamically [after generation of those csv files]) in a separate page to download those csv files. how to do this in asp.net (C#)? its urgent.
View 1 Replies View RelatedHello All,
Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank.
Thanks in advance for your reply.
Koann
I want to convert table into following xml format. Sample two records is given below.
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>
[Code] ....
Hello,
Any help here much appreciated.
I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.
i suspect this is when i run a cursor through the table.
Is anyone able to confirm for me whether running a cursor changes a table's properties?
Many thanks.
Tim
Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
i want to convert the rows of one table to be the column of another table in run time......i have two tables one having student id and name and in another table there are student id, marks and subject as english. hindi and maths in rows...i want to make another a third table which contains student id , name , marks as english, hindi, maths as the column....tried a lot but didnt get the right way...do we have to do it through Join or is there some other technique by which we can solve the query........i dont know wheather i am really informative or not..but am sitting online if incase you wanna know something else even..........
View 4 Replies View RelatedI'm going through and restructuring a database scheme and was wondering if, after I make a query (view), how can I convert that into a table?
Thanks
Hello,
Can anyone tell me, how to convert an rtf string to text string using a function in SQL Server? I got a table in SQL Server database with field datatype text(16), which holds an rtf. I've to extract it as a text using a function.
thank you,
Code snippet for changing '00001001001' into a table value construct containing (64,8,1)?
View 6 Replies View RelatedI want to convert all the column of table to ToBase64String.
Public Class ScriptMain
Inherits UserComponent
Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)
Dim hash As Byte() = md5.ComputeHash(columnContents)
Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)
Row.RowChecksum = hashString
End Sub
End Class
I want to convert xml data with select query and insert into table.
Xml sample like this :
<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
[code]....
And data should be like this with select query :
NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL
I want simple query in form of above format.
i have following table columns and i want to convert these all columns into row
SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]
[Code] ....
required output looks like
CASHINHAND 118950
CASHATBANK Â 200
DEPOSITS 3000
ACCRECEIVABLE
25000
I have one of the sample values in my table. I need to convert below value to Decimal(18,5)
DECLARE @i
VARCHAR
SET @i
= '0.9'
Output i m looking for is 0.90000