tsql - Create Tree Query From Numeric Mapping Table in SQL -
i have exported table accounting software below.
accountid accountname --------- ----------- 11 acc11 12 acc12 13 acc13 11/11 acc11/11 11/12 acc11/12 11/111 acc11/111 11/11/001 acc11/11/001 11/11/002 acc11/11/002 12/111 acc12/111 12/112 acc12/112
then want convert tree query in ms-sql server 2008 use treelist datasource in win aaplication.
expected sql query result:
accountid accountname id parentid level haschild --------- ----------- --- --------- ------ -------- 11 acc11 1 null 1 1 12 acc12 2 null 1 1 13 acc13 3 null 1 0 11/11 acc11/11 4 1 2 1 11/12 acc11/12 5 1 2 0 11/111 acc11/111 6 1 2 0 11/11/001 acc11/11/001 7 4 3 0 11/11/002 acc11/11/002 8 4 3 0 12/111 acc12/111 9 2 2 0 12/112 acc12/112 10 2 2 0
would please me creating sql query? thanks.
no doubts,whatever query write or whatever dba do,your query slow because of faulty table structure.
i believe above query working fine other sample data also.
in such situation,i fetch record in front end (c# etc) , manipulation there in order desire output,it fast in such cases.
i have tried write query in own way,hope fast 6000 records.also please test query other sample data also.
i find lastindexof("/") join
declare @filepath varchar(50) = '11/11/001' declare @findchar1 varchar(1) = '/' select substring (@filepath,0, len(@filepath) - charindex(@findchar1,reverse(@filepath))+1 )as lastoccuredat
final query,
declare @tbl table(id int identity(1,1), accountid varchar(100), accountname varchar(100)); insert @tbl values ('11','acc11') ,('12','acc12') ,('13','acc13') ,('11/11','acc11/11') ,('11/12','acc11/12') ,('11/111','acc11/111') ,('11/11/001','acc11/11/001') ,('11/11/002','acc11/11/002') ,('12/111','acc12/111') ,('12/112','acc12/112'); declare @findchar varchar(1) = '/' select a.id ,a.accountid ,a.accountname ,( select min(c.id) @tbl c c.id < a.id , substring(a.accountid, 0, len(a.accountid) - charindex(@findchar, reverse(a.accountid)) + 1) = c.accountid ) parentid ,len(a.accountid) - len(replace(a.accountid, '/', '')) + 1 [level] ,( select case when min(c.id) not null 1 else 0 end @tbl c c.id > a.id , substring(c.accountid, 0, len(c.accountid) - charindex(@findchar, reverse(c.accountid)) + 1) = a.accountid ) haschild @tbl inner join @tbl b on a.id = b.id + 1
Comments
Post a Comment