query

• peterausger

  Mr or Mrs. 500

  Points: 544

  More actions

  June 8, 2015 at 5:03 am

  #301382

  Dear all,

  Can you please help me to create following op?

  create table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

  insert into test222 values (1, '2323', 0, 'iam a boy')

  insert into test222 values (2, '23231000', 1, 'boy')

  insert into test222 values (3, '23232', 1, 'boo')

  insert into test222 values (4, '232321', 3, 'bo')

  insert into test222 values (5, '23232110', 4, 'boyy')

  insert into test222 values (6, '23232190', 4, 'gril')

  insert into test222 values (7, '232329', 3, 'body')

  insert into test222 values (8, '23232910', 7, 'girll')

  insert into test222 values (9, '23232990', 7, 'boy')

  insert into test222 values (10, '23233000', 1, 'bo')

  insert into test222 values (11, '232390', 1, 'nh')

  insert into test222 values (12, '23239010', 10, 'ui')

  insert into test222 values (13, '23239020', 10, 'dert')

  insert into test222 values (14, '23239030', 10, 'hyui')

  insert into test222 values (15, '23239040', 10, 'nji')

  insert into test222 values (16, '23239090', 10, 'vfr')

  insert into test222 values (17, '2345', 0, 'boo')

  insert into test222 values (18, '23455', 17, 'bo')

  I have above records and

  i want following op when i search for girll

  sid scode Parentid sName Hierarchy Level

  123230 iam a boy23231

  323232 1 boo 2323\23232 2

  7232329 3 body2323\23232\232329 3

  8232329107 girll2323\23232\232329\232329104

  i want following op when i search for boyy

  sid scode Parentid sName Hierarchy Level

  123230iam a boy 2323 1

  3232321boo 2323\23232 2

  42323213bo 2323\23232\232321 3

  5232321104boyy 2323\23232\232321\23232110 4

  Can you guys please help me?

  Thanks

  Peter

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 8, 2015 at 7:12 am

  #1803656

  This article should put you in the right direction: http://www.sqlservercentral.com/articles/Hierarchy/94040/

  -- Gianluca Sartori