0

I have a table tbl in SQL Server. Here's what the data in that table looks like:

enter image description here

In SystemPartLevel I save the value of parent (SystemPartID)

I have to show these records in a treeview in C# like this:

enter image description here

I need a SQL query that will show all children of 'A' or 'B'.

I want to see all child of every node.

For example for 'A' = 

{A1, A2, A1-1, A1-2, A1-1-1}

or for A1 = 

{A1-1, A1-2, A1-1-1}

How can I write this query?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
saeed
  • 49
  • 2
  • 8
  • If you are looking for any node related to `A` then you could simply query `select * from tbl where left(tbl.SystemPartName, 1) = 'A'` and if you are looking for all nodes below `A1` then you could query `select * from tbl where left(tbl.SystemPartName, 2) = 'A1'`. Alternatively, you could pass the search-term to the query as use `select * from tbl where tbl.SystemPartName like @SearchTerm + "%"` – Ralph Dec 13 '16 at 11:32
  • 1
    Possible duplicate of [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) – Sefe Dec 13 '16 at 11:33
  • Essentially this requires string aggregation, which is simple in postgres but a little tricky in SQL server. Check out [this post](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) for how to do this in SQL Server. – Henning Koehler Dec 13 '16 at 11:34
  • @Ralph no this is incorrect because this data is sample and i cant use left() statement. – saeed Dec 13 '16 at 11:35
  • 2
    http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Dec 13 '16 at 11:45
  • This is not the correct way to store hierarchial data in sql server. The correct way is for each item to have a parent id (that would be null for root items). Store your data like this and use a recursive cte to get the hierarchy – ATC Dec 13 '16 at 19:14

0 Answers0