I have tabel with id(int) and parentid(int) column. I would like to build hierarchy from top group to lowest child group. Most top group parentid value is NULL How can I achieve this?

result should be:

  1. /254908416
  2. /362858460/468059136/317456352
  3. /362858460/871041025/387747840

etc Data:

  1. 254908416;
  2. 362858460;
  3. 617330496;887656451
  4. 1966161919;362858460
  5. 1405662208;1966161919
  6. 1244715002;1966161919
  7. 1077692408;1966161919
  8. 791121919;362858460
  9. 837370882;791121919
  10. 1468407808;791121919
  11. 733566972;791121919
  12. 871041025;362858460
  13. 1860938766;362858460
  14. 887656451;362858460
  15. 367575040;362858460
  16. 1391026193;362858460
  17. 468059136;362858460
  18. 2078713856;362858460
  19. 182920208;1391026193
  20. 1964555247;1391026193
  21. 1592067076;1391026193
  22. 434356226;2078713856
  23. 1994440704;2078713856
  24. 752940031;871041025
  25. 387747840;871041025
  26. 1159417875;887656451
  27. 988534764;887656451
  28. 1777662972;887656451
  29. 1513630728;887656451
  30. 1554393071;468059136
  31. 882323488;468059136
  32. 317456352;468059136
  33. 2081736736;468059136
  34. 434795488;468059136
  35. 1106030556;468059136
  36. 873465892;468059136
  37. 1712364548;468059136
  38. 1190012893;367575040
  39. 66089944;367575040
  40. 1158434817;367575040
  41. 1393349632;367575040
  42. 1475515350;367575040
  43. 1091389440;367575040
  44. 302198784;367575040
  45. 1560301582;367575040
  46. etc...

asked 19 Aug '13, 04:50

Tanel's gravatar image

Tanel
16113
accept rate: 0%


What SQL Anywhere version are you using?

Starting with v9, a recursive common table expression is handy for such tasks - cf.

permanent link

answered 19 Aug '13, 07:34

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663
accept rate: 32%

Yep- recursive union did the trick.

Thx

(22 Aug '13, 03:50) Tanel
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 19 Aug '13, 04:50

question was seen: 822 times

last updated: 23 Aug '13, 03:57