sql server - Create Tree Query From Numeric Mapping Table in SQL (Specific Format) -

-

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

i want convert tree query in ms-sql server 2008 use treelist datasource in win aaplication. i raised question before , it's answered way very slow big table more 5000 records (create tree query numeric mapping table in sql). think counting "/" , separating accountid field "/" can solve problem easier , faster. anyway, expected result must below:

   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

please me.

i modified answer given in first question...

it best, if table keep relation data directly in indexed columns. before change table's structure might try this:

a table test data

declare @tbl table ( 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');

this needed data newly created temp table called #temphierarchy

select accountid       ,accountname       ,row_number() over(order len(accountid)-len(replace(accountid,'/','')),accountid) id       ,extended.hierarchylevel       ,stuff(        (          select '/' + a.b.value('.','varchar(10)')          extended.idsxml.nodes('/x[position() <= sql:column("hierarchylevel")]') a(b)          xml path('')        ),1,2,'') parentpath       ,extended.idsxml.value('/x[sql:column("hierarchylevel")+1][1]','varchar(10)') ownid       ,extended.idsxml.value('/x[sql:column("hierarchylevel")][1]','varchar(10)') ancestorid #temphierarchy @tbl cross apply(select len(accountid)-len(replace(accountid,'/','')) + 1 hierarchylevel                   ,cast('<x></x><x>' + replace(accountid,'/','</x><x>') + '</x>' xml) idsxml) extended ;

the intermediate result

+-----------+--------------+----+----------------+------------+-------+------------+ | accountid | accountname  | id | hierarchylevel | parentpath | ownid | ancestorid | +-----------+--------------+----+----------------+------------+-------+------------+ | 11        | acc11        | 1  | 1              |            | 11    |            | +-----------+--------------+----+----------------+------------+-------+------------+ | 12        | acc12        | 2  | 1              |            | 12    |            | +-----------+--------------+----+----------------+------------+-------+------------+ | 13        | acc13        | 3  | 1              |            | 13    |            | +-----------+--------------+----+----------------+------------+-------+------------+ | 11/11     | acc11/11     | 4  | 2              | 11         | 11    | 11         | +-----------+--------------+----+----------------+------------+-------+------------+ | 11/111    | acc11/111    | 5  | 2              | 11         | 111   | 11         | +-----------+--------------+----+----------------+------------+-------+------------+ | 11/12     | acc11/12     | 6  | 2              | 11         | 12    | 11         | +-----------+--------------+----+----------------+------------+-------+------------+ | 12/111    | acc12/111    | 7  | 2              | 12         | 111   | 12         | +-----------+--------------+----+----------------+------------+-------+------------+ | 12/112    | acc12/112    | 8  | 2              | 12         | 112   | 12         | +-----------+--------------+----+----------------+------------+-------+------------+ | 11/11/001 | acc11/11/001 | 9  | 3              | 11/11      | 001   | 11         | +-----------+--------------+----+----------------+------------+-------+------------+ | 11/11/002 | acc11/11/002 | 10 | 3              | 11/11      | 002   | 11         | +-----------+--------------+----+----------------+------------+-------+------------+

and similar recursive approach takes place in first answer. - using real table , string splitting has taken place - should faster...

with recursivecte (     select th.*            ,cast(null bigint) parentid             ,case when exists(select 1 #temphierarchy x x.parentpath=th.accountid) 1 else 0 end haschild     #temphierarchy th th.hierarchylevel=1     union     select sa.accountid           ,sa.accountname           ,sa.id           ,sa.hierarchylevel           ,sa.parentpath           ,sa.ownid           ,sa.ancestorid           ,(select x.id #temphierarchy x x.accountid=sa.parentpath)           ,case when exists(select 1 #temphierarchy x x.parentpath=sa.accountid) 1 else 0 end haschild     recursivecte r     inner join #temphierarchy sa on sa.hierarchylevel=r.hierarchylevel+1                                         , r.accountid=sa.parentpath ) select r.accountid       ,r.accountname       ,r.id       ,r.parentid       ,r.hierarchylevel       ,r.haschild recursivecte r order hierarchylevel,parentid;

and clean up

drop table #temphierarchy;

and here's final result

+-----------+--------------+----+----------+----------------+----------+ | accountid | accountname  | id | parentid | hierarchylevel | 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/111    | acc11/111    | 5  | 1        | 2              | 0        | +-----------+--------------+----+----------+----------------+----------+ | 11/12     | acc11/12     | 6  | 1        | 2              | 0        | +-----------+--------------+----+----------+----------------+----------+ | 12/111    | acc12/111    | 7  | 2        | 2              | 0        | +-----------+--------------+----+----------+----------------+----------+ | 12/112    | acc12/112    | 8  | 2        | 2              | 0        | +-----------+--------------+----+----------+----------------+----------+ | 11/11/001 | acc11/11/001 | 9  | 4        | 3              | 0        | +-----------+--------------+----+----------+----------------+----------+ | 11/11/002 | acc11/11/002 | 10 | 4        | 3              | 0        | +-----------+--------------+----+----------+----------------+----------+

Comments

Post a Comment

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

-
i trying fetch records database , having above error this classs appropriate getters , setters public class user { private int id; private string username; private string password; private string email; i have create database , inserted data below configuration <bean id="datasource" class="org.apache.commons.dbcp2.basicdatasource" destroy-method="close"> <property name="driverclassname" value="com.mysql.jdbc.driver"/> <property name="url" value="jdbc:mysql://localhost:3306/usersdb"/> <property name="username" value="root"/> <property name="password" value=""/> </bean> this complete stacktrace severe: servlet.service() servlet [appservlet] in context path [/spring] threw exception [request processing failed; nested exception org.hibernate.exception.sqlgrammarexception: not ...
Read more

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

-
Image
i have postgresql db number of tables. if query: select column_name information_schema.columns table_name="my_table"; i list of columns returned properly. however, when query: select * "my_table"; i error: (programmingerror) relation "my_table" not exist 'select *\n "my_table"\n' {} any thoughts on why can columns, can't query table? goal able query table. you have include schema if isnt public one select * <schema>."my_table" or can change default schema show search_path; set search_path my_schema; check table schema here select * information_schema.columns for example if table on default schema public both works ok select * parroquias_region select * public.parroquias_region but sectors need specify schema select * map_update.sectores_point
Read more

asp.net mvc - breakpoint on javascript in CSHTML? -

-
i have .cshtml file has javascript tag. within script tag have few lines of javascript. first line of javascript refers property in @viewbag. other javascript lines plain script without mvc related references. observation: can place breakpoints (visual studio 2015) on each of these javascript lines. however, breakpoints has white dot in center of breakpoint symbol (red circle) except line @viewbag reference (this particular line not have white dot in center). it seems breakpoint without white dot hit during run-time. question: can explain going on here? white dot mean? why plain red breakpoint line line hitting breakpoint? the visual studio debugger expecting debug actual server-side code within .cshtml file opposed client-side javascript within it. iirc, visual studio allow debug javascript code expected in internet explorer (and possibly edge), other browsers want use either third-party tool or developer tools (f12) within browser. an easy approach take advant...
Read more