HElp with a query!!

• xRafo

  SSChasing Mays

  Points: 623

  More actions

  August 28, 2013 at 3:28 pm

  #276619

  Hi all i have this data:

  DECLARE @data table (id int,name varchar(max),parent_id int,node_type_id int,node_order INT)

  INSERT @data

  SELECT 2,'ClarkDietrich',NULL,1,1 UNION ALL

  SELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALL

  SELECT 294,'3.29',2,6,2 UNION ALL

  SELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALL

  SELECT 296,NULL,295,8,0 UNION ALL

  SELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALL

  SELECT 298,NULL,297,8,0 UNION ALL

  SELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALL

  SELECT 300,NULL,299,8,0 UNION ALL

  SELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALL

  SELECT 302,NULL,301,8,0 UNION ALL

  SELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALL

  SELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALL

  SELECT 310,'TS_3.29.0.0',2,2,3 UNION ALL

  SELECT 311,'TS_MASTER',2,2,0 UNION ALL

  SELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALL

  SELECT 313,NULL,312,4,0 UNION ALL

  SELECT 314,NULL,313,9,0 UNION ALL

  SELECT 315,NULL,313,9,0 UNION ALL

  SELECT 316,NULL,313,9,0 UNION ALL

  SELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALL

  SELECT 318,NULL,317,4,0 UNION ALL

  SELECT 319,NULL,318,9,0 UNION ALL

  SELECT 320,NULL,318,9,0 UNION ALL

  SELECT 321,NULL,318,9,0 UNION ALL

  SELECT 322,NULL,318,9,0 UNION ALL

  SELECT 323,NULL,318,9,0 UNION ALL

  SELECT 325,'TS_IM5972',310,2,1 UNION ALL

  SELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALL

  SELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALL

  SELECT 328,NULL,327,4,0 UNION ALL

  SELECT 329,'TS_RFDEVICES',311,2,2 UNION ALL

  SELECT 330,NULL,328,9,0 UNION ALL

  SELECT 331,NULL,328,9,0 UNION ALL

  SELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALL

  SELECT 333,NULL,332,4,0 UNION ALL

  SELECT 334,NULL,328,9,0 UNION ALL

  SELECT 335,NULL,328,9,0 UNION ALL

  SELECT 336,NULL,328,9,0 UNION ALL

  SELECT 337,NULL,333,9,0 UNION ALL

  SELECT 338,NULL,328,9,0 UNION ALL

  SELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALL

  SELECT 340,NULL,339,4,0 UNION ALL

  SELECT 341,NULL,340,9,0 UNION ALL

  SELECT 342,NULL,340,9,0 UNION ALL

  SELECT 343,NULL,340,9,0 UNION ALL

  SELECT 344,NULL,340,9,0 UNION ALL

  SELECT 345,NULL,340,9,0 UNION ALL

  SELECT 346,NULL,340,9,0 UNION ALL

  SELECT 347,NULL,333,9,0 UNION ALL

  SELECT 349,'TS_IM5995',310,2,2 UNION ALL

  SELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALL

  SELECT 355,NULL,354,4,0 UNION ALL

  SELECT 356,NULL,355,9,0 UNION ALL

  SELECT 357,NULL,355,9,0 UNION ALL

  SELECT 358,NULL,355,9,0 UNION ALL

  SELECT 359,NULL,355,9,0 UNION ALL

  SELECT 360,NULL,355,9,0 UNION ALL

  SELECT 361,NULL,355,9,0 UNION ALL

  SELECT 362,NULL,355,9,0 UNION ALL

  SELECT 363,NULL,355,9,0 UNION ALL

  SELECT 364,NULL,355,9,0 UNION ALL

  SELECT 365,NULL,355,9,0 UNION ALL

  SELECT 366,NULL,355,9,0 UNION ALL

  SELECT 367,NULL,302,10,0 UNION ALL

  SELECT 368,NULL,333,9,0 UNION ALL

  SELECT 369,NULL,333,9,0 UNION ALL

  SELECT 370,NULL,333,9,0 UNION ALL

  SELECT 371,NULL,333,9,0 UNION ALL

  SELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALL

  SELECT 384,NULL,383,4,0 UNION ALL

  SELECT 385,NULL,384,9,0 UNION ALL

  SELECT 386,NULL,384,9,0 UNION ALL

  SELECT 387,NULL,384,9,0 UNION ALL

  SELECT 388,NULL,384,9,0 UNION ALL

  SELECT 389,NULL,384,9,0 UNION ALL

  SELECT 390,NULL,384,9,0 UNION ALL

  SELECT 391,'TS_INVENTORY',311,2,3 UNION ALL

  SELECT 392,'TS_SETUP',391,2,1 UNION ALL

  SELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALL

  SELECT 394,NULL,393,8,0 UNION ALL

  SELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALL

  SELECT 396,NULL,395,4,0 UNION ALL

  SELECT 397,'TS_TRAFFIC',311,2,4 UNION ALL

  SELECT 398,NULL,396,9,0 UNION ALL

  SELECT 399,NULL,396,9,0 UNION ALL

  SELECT 400,NULL,396,9,0 UNION ALL

  SELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALL

  SELECT 402,NULL,401,4,0 UNION ALL

  SELECT 403,NULL,396,9,0 UNION ALL

  SELECT 405,'TS_IM5932',310,2,3 UNION ALL

  SELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALL

  SELECT 407,NULL,406,4,0 UNION ALL

  SELECT 408,NULL,407,9,0 UNION ALL

  SELECT 409,NULL,407,9,0 UNION ALL

  SELECT 410,NULL,407,9,0 UNION ALL

  SELECT 411,NULL,407,9,0 UNION ALL

  SELECT 412,NULL,402,9,0 UNION ALL

  SELECT 414,NULL,402,9,0 UNION ALL

  SELECT 415,NULL,402,9,0 UNION ALL

  SELECT 416,NULL,402,9,0 UNION ALL

  SELECT 417,NULL,402,9,0 UNION ALL

  SELECT 418,'TS_IM6039',310,2,4 UNION ALL

  SELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALL

  SELECT 420,NULL,419,4,0 UNION ALL

  SELECT 421,NULL,420,9,0 UNION ALL

  SELECT 422,NULL,420,9,0 UNION ALL

  SELECT 423,NULL,420,9,0 UNION ALL

  SELECT 424,NULL,420,9,0 UNION ALL

  SELECT 425,NULL,420,9,0 UNION ALL

  SELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALL

  SELECT 294,'3.29',2,6,2 UNION ALL

  SELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALL

  SELECT 296,NULL,295,8,0 UNION ALL

  SELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALL

  SELECT 298,NULL,297,8,0 UNION ALL

  SELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALL

  SELECT 300,NULL,299,8,0 UNION ALL

  SELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALL

  SELECT 302,NULL,301,8,0 UNION ALL

  SELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALL

  SELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALL

  SELECT 310,'TS_3.29.0.0',2,2,3 UNION ALL

  SELECT 311,'TS_MASTER',2,2,0 UNION ALL

  SELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALL

  SELECT 313,NULL,312,4,0 UNION ALL

  SELECT 314,NULL,313,9,0 UNION ALL

  SELECT 315,NULL,313,9,0 UNION ALL

  SELECT 316,NULL,313,9,0 UNION ALL

  SELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALL

  SELECT 318,NULL,317,4,0 UNION ALL

  SELECT 319,NULL,318,9,0 UNION ALL

  SELECT 320,NULL,318,9,0 UNION ALL

  SELECT 321,NULL,318,9,0 UNION ALL

  SELECT 322,NULL,318,9,0 UNION ALL

  SELECT 323,NULL,318,9,0 UNION ALL

  SELECT 325,'TS_IM5972',310,2,1 UNION ALL

  SELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALL

  SELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALL

  SELECT 328,NULL,327,4,0 UNION ALL

  SELECT 329,'TS_RFDEVICES',311,2,2 UNION ALL

  SELECT 330,NULL,328,9,0 UNION ALL

  SELECT 331,NULL,328,9,0 UNION ALL

  SELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALL

  SELECT 333,NULL,332,4,0 UNION ALL

  SELECT 334,NULL,328,9,0 UNION ALL

  SELECT 335,NULL,328,9,0 UNION ALL

  SELECT 336,NULL,328,9,0 UNION ALL

  SELECT 337,NULL,333,9,0 UNION ALL

  SELECT 338,NULL,328,9,0 UNION ALL

  SELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALL

  SELECT 340,NULL,339,4,0 UNION ALL

  SELECT 341,NULL,340,9,0 UNION ALL

  SELECT 342,NULL,340,9,0 UNION ALL

  SELECT 343,NULL,340,9,0 UNION ALL

  SELECT 344,NULL,340,9,0 UNION ALL

  SELECT 345,NULL,340,9,0 UNION ALL

  SELECT 346,NULL,340,9,0 UNION ALL

  SELECT 347,NULL,333,9,0 UNION ALL

  SELECT 349,'TS_IM5995',310,2,2 UNION ALL

  SELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALL

  SELECT 355,NULL,354,4,0 UNION ALL

  SELECT 356,NULL,355,9,0 UNION ALL

  SELECT 357,NULL,355,9,0 UNION ALL

  SELECT 358,NULL,355,9,0 UNION ALL

  SELECT 359,NULL,355,9,0 UNION ALL

  SELECT 360,NULL,355,9,0 UNION ALL

  SELECT 361,NULL,355,9,0 UNION ALL

  SELECT 362,NULL,355,9,0 UNION ALL

  SELECT 363,NULL,355,9,0 UNION ALL

  SELECT 364,NULL,355,9,0 UNION ALL

  SELECT 365,NULL,355,9,0 UNION ALL

  SELECT 366,NULL,355,9,0 UNION ALL

  SELECT 367,NULL,302,10,0 UNION ALL

  SELECT 368,NULL,333,9,0 UNION ALL

  SELECT 369,NULL,333,9,0 UNION ALL

  SELECT 370,NULL,333,9,0 UNION ALL

  SELECT 371,NULL,333,9,0 UNION ALL

  SELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALL

  SELECT 384,NULL,383,4,0 UNION ALL

  SELECT 385,NULL,384,9,0 UNION ALL

  SELECT 386,NULL,384,9,0 UNION ALL

  SELECT 387,NULL,384,9,0 UNION ALL

  SELECT 388,NULL,384,9,0 UNION ALL

  SELECT 389,NULL,384,9,0 UNION ALL

  SELECT 390,NULL,384,9,0 UNION ALL

  SELECT 391,'TS_INVENTORY',311,2,3 UNION ALL

  SELECT 392,'TS_SETUP',391,2,1 UNION ALL

  SELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALL

  SELECT 394,NULL,393,8,0 UNION ALL

  SELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALL

  SELECT 396,NULL,395,4,0 UNION ALL

  SELECT 397,'TS_TRAFFIC',311,2,4 UNION ALL

  SELECT 398,NULL,396,9,0 UNION ALL

  SELECT 399,NULL,396,9,0 UNION ALL

  SELECT 400,NULL,396,9,0 UNION ALL

  SELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALL

  SELECT 402,NULL,401,4,0 UNION ALL

  SELECT 403,NULL,396,9,0 UNION ALL

  SELECT 405,'TS_IM5932',310,2,3 UNION ALL

  SELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALL

  SELECT 407,NULL,406,4,0 UNION ALL

  SELECT 408,NULL,407,9,0 UNION ALL

  SELECT 409,NULL,407,9,0 UNION ALL

  SELECT 410,NULL,407,9,0 UNION ALL

  SELECT 411,NULL,407,9,0 UNION ALL

  SELECT 412,NULL,402,9,0 UNION ALL

  SELECT 414,NULL,402,9,0 UNION ALL

  SELECT 415,NULL,402,9,0 UNION ALL

  SELECT 416,NULL,402,9,0 UNION ALL

  SELECT 417,NULL,402,9,0 UNION ALL

  SELECT 418,'TS_IM6039',310,2,4 UNION ALL

  SELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALL

  SELECT 420,NULL,419,4,0 UNION ALL

  SELECT 421,NULL,420,9,0 UNION ALL

  SELECT 422,NULL,420,9,0 UNION ALL

  SELECT 423,NULL,420,9,0 UNION ALL

  SELECT 424,NULL,420,9,0 UNION ALL

  SELECT 425,NULL,420,9,0

  SELECT * FROM @data

  This table have the relationship to itself by the parent_id column, I would sort through so you can display hierarchically.

  Anyone can help me with this?..

  thanks!

  ____________________________________________________________________________
  Rafo*

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  August 28, 2013 at 5:16 pm

  #1645786

  The following article should help...

  http://qa.sqlservercentral.com/articles/T-SQL/72503/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• xRafo

  SSChasing Mays

  Points: 623

  More actions

  August 29, 2013 at 8:16 am

  #1646006

  thank you very much.

  ____________________________________________________________________________
  Rafo*