Convert A Table Into Tree
Aug 21, 2007
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...
View 1 Replies
ADVERTISEMENT
Apr 6, 2015
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
View 2 Replies
View Related
Sep 12, 2006
Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy
View 4 Replies
View Related
Dec 11, 2006
hello friends!
Can any body guide me regarding Tree/hierarchy Multi-Referential table structure.
What i have is only for known tree structure i.e upto 3 or 4 level but if i waanna to find nth level hoe shud i design my table structure.
T.I.A
View 3 Replies
View Related
Jan 15, 2015
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
INSERT INTO #T1 VALUES
( 200,3, '200-3-2-1' ),
( 100 , 14 , '100-14-1'),
( 3 , 2 , '3-2-1'),
( 2 , 1 , '2-1'),
( 14 , 1 , '14-1'),
( 1 , NULL , '1');
[Code] .....
View 2 Replies
View Related
Apr 7, 2007
Hi all
I have a Table with Following structure ( a Tree Structure )
PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105
The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?
PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100
Any help greatly would be appreciated.
Kind Regards.
View 2 Replies
View Related
Apr 4, 2008
Hi.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
Thanks
Novi
View 2 Replies
View Related
Jul 20, 2005
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 Related
Dec 7, 2005
Hi
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.
View 4 Replies
View Related
Mar 4, 2008
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?
View 3 Replies
View Related
Mar 4, 2008
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?
View 3 Replies
View Related
Apr 12, 2008
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
View 8 Replies
View Related
Jul 20, 2005
Hi,how to:using dts convert row in table to column.i have table:col1,col2b1 , 1b2 , 2b1 , 4b3 , 3b2 , 5and i want using dts convert/rewrite this table to another table:b1,b2,b31,null,nullnull,2,null4,null,nullnull,null,3null,5,null
View 1 Replies
View Related
May 9, 2008
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?
View 1 Replies
View Related
May 6, 2004
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?
View 3 Replies
View Related
Jan 17, 2007
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
View 1 Replies
View Related
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Apr 27, 2008
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 Related
Apr 23, 2004
I 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
View 3 Replies
View Related
Feb 15, 2006
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.
View 1 Replies
View Related
Oct 31, 2006
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
View 2 Replies
View Related
Sep 12, 2007
Hi
I would like to convert sp_who into a table/query. Please help
Thanks
View 3 Replies
View Related
Nov 11, 2011
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 Related
Apr 9, 2008
Hi
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
View 4 Replies
View Related
Sep 12, 2007
Hi
How to convert a sp_who in into a table/query
Please help
Raj.
View 2 Replies
View Related
Sep 29, 2005
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 Related
Sep 20, 2007
Hi 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.
View 3 Replies
View Related
May 30, 2008
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 Related
Sep 12, 2000
Hello 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
View 2 Replies
View Related
Oct 5, 2015
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] ....
View 9 Replies
View Related
Apr 10, 2008
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.
View 3 Replies
View Related
Mar 7, 2008
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 Related
Sep 11, 2007
I'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
View 4 Replies
View Related
