Accessing hierarchial data and transforming that in C#

Question

Hi All,

     Here is my task and i need an advice on how to approach this..

1.I have an hierarchial data in sql server all in on table for ex:

 Category                                              Code   

   001                                                      1900

 001 #@ 100                                           1900

001 #@ 100 # @ 101                              1900

001#@ 100 #@101#@102                      1900

the hierachy is as defined above in the table:

001 is the first or higest level and the next row which has 001#@100 shows 001 as the hiest level and 100 shows the next level and same for other rows.so essentially i have  4 levels of hierarchy.The table i am reading from is flattened.

My task is to read from this table and see if the higest level of the hierarchy has the same code as its childs then insert just one row with the higest level for example: the result table shd hold only

category              code

001                      1900

this shd be true for any level...

Higest level of hierarchy has 2 values and next level has around 100 and the next has 100+ each

please suggest...

                                                     this is how the hierarchy looks:

                                                           Higest Level (Around 2)

                                                                     |

                                                           |---------------|

                                               Level 2                  Level 2   (This level has around 100)

                                                   |                              |

                                           |------------|                   |-------------|

                                      Level 3        Level 3       Level 3          Level 3   (This Level has around 150+ each which will be 600+)

and there is one more level ..

Please suggest an solution..

Answer 1

Well, for you output it will be something along the lines of 

SELECT *
FROM table
WHERE NOT (Category CONTAINS('#@'))

For the validation

SELECT *
FROM table
JOIN
(SELECT *
FROM table
WHERE NOT (Category CONTAINS '#@') ) as results
ON table.Category LIKE results.Category + '%'
WHERE table.Code != results.Code

that should give you all of the invalid rows.  Note this is all psudocode off off of the top of my head.  It's also not really C# related, but whatever.

Answer 2

Hi RAGS1109,

I am still not clear about your situation after reading your thread for several times...
Anyway, does servy42's suggestion make sense to you?

Have a nice day,

---

Leo Liu [MSFT]
MSDN Community Support | Feedback to us