Recursion Or Reading Tree Data

Last Post 07 Nov 2011 07:36 AM by dbandee. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 Jul 2010 03:55 AM


Can some please help me to write the sql query to extract the data from the following layout?

Key               Value
User1              Group1,Group2
Group1            SubGroup11,SubGroup12,SubGroup13
Group2            SubGroup21,SubGroup22
SubGroup11    12341,12332,33332
SubGroup12    12344
SubGroup13    44444
SubGroup21    77777
SubGroup22    88888,99999
User2              Group3
Group3            SubGroup31,SubGroup32
SubGroup31    33333
SubGroup32    66666,99988

At the end I to read this tree and p*** the data as shown below

User1            12341
User1            12332
User1            33332
User1            12344
User1            44444
User1            77777
User1            88888
User1            99999
User2            33333
User2            66666
User2            99988


New Member
New Member

07 Nov 2011 07:36 AM
First i would normalize this table, it clearly violates 1NF (or create a function so it looks like you have normalized table, or temp table)
Second use CTE to find parent group for each user and than get the values
All the code you need is here

hope it helps

Acceptable Use Policy