locked
Need help with parent child query RRS feed

  • Question

  • I have a table as below. It only has one child level

    ID | Name | Parent
    1 | KSP | 0
    2 | PSP | 0
    3 | HJI | 1
    4 | MXM | 2


    I want the output like : basically all parent should follow thier child and order on parent name

    ID | Name | Parent
    1 | KSP | 0
    3 | HJI | 1
    2 | PSP | 0
    4 | MXM | 2

    Thanks for help

    Thanks
    Friday, January 20, 2012 7:57 PM

Answers

  • Try.

    Declare @source table(ID int, Name varchar(10), Parent Int) 
    insert into @source
    select 1,'KSP',0 
    union all
    select 2 ,'PSP', 0
    union all
    select 3 ,'HJI', 1
    union all
    select 4 ,'MXM', 2
    select * from @source
    order by case when parent=0 then ID else Parent end,case when parent=0 then 0 else 1 end
    



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Proposed as answer by Naomi N Friday, January 20, 2012 8:42 PM
    • Marked as answer by MKyE Friday, January 20, 2012 9:06 PM
    Friday, January 20, 2012 8:39 PM

All replies

  • You can learn how to do it from this thread.

    Show all children and grandchildren for parent hierarchically
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cd37dd9f-17c5-4df5-b072-5a9e51d1798d

     


    AMB

    Some guidelines for posting questions...

    Friday, January 20, 2012 8:27 PM
  • Try.

    Declare @source table(ID int, Name varchar(10), Parent Int) 
    insert into @source
    select 1,'KSP',0 
    union all
    select 2 ,'PSP', 0
    union all
    select 3 ,'HJI', 1
    union all
    select 4 ,'MXM', 2
    select * from @source
    order by case when parent=0 then ID else Parent end,case when parent=0 then 0 else 1 end
    



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Proposed as answer by Naomi N Friday, January 20, 2012 8:42 PM
    • Marked as answer by MKyE Friday, January 20, 2012 9:06 PM
    Friday, January 20, 2012 8:39 PM
  • Thanks so much Vinay, your solution was spot on.

     


    Thanks
    Friday, January 20, 2012 9:05 PM
  • "A problem well stated is a problem half solved." -- Charles F. Kettering
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
    What you have is called the adjacency list model for a hierarchy. Look up the Nested Sets model so you will not have to write procedural code, such as recursive CTEs or cursors. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Saturday, January 21, 2012 5:50 AM
  • Good trick and use of union all with case statement.

     

    Saturday, January 21, 2012 1:11 PM