t-sql need a rollup view from hierarchal data

Question

I have an sql-server 2012 table that looks like this:

lvl  | keywords
-----|------------ 
A    | null
A1   | null
A1.1 | red, green
A1.2 | blue
A1.3 | orange, yellow
A2   | null
A2.1 | brown
A2.2 | black, purple
B    | null
B1   | null
B1.1 | pink
B1.2 | velvet, orange
B2   | null
B2.1 | cyan
B2.2 | purple, white
etc.

I need a view that rolls up the keywords to the higher level. So top level A must have all the keywords from the underlying records where column lvl starts with A. Level A1 should have all the keywords from the records where column lvl starts with A1, etc. So the output of this view should be as follows:

lvl  | keywords
-----|------------ 
A    | red, green, blue, orange, yellow, brown, black, purple
A1   | red, green, blue, orange, yellow
A1.1 | red, green
A1.2 | blue
A1.3 | orange, yellow
A2   | brown, black, purple
A2.1 | brown
A2.2 | black, purple
B    | pink, velvet, orange, cyan, purple, white
B1   | pink, velvet, orange
B1.1 | pink
B1.2 | velvet, orange
B2   | cyan, purple, white
B2.1 | cyan
B2.2 | purple, white
etc.

How to do that in T-SQL? (sql-server 2012)

You will need a lot of string processing accomplish this. If you can redesign your table, have columns for each level. So your table will have - Level1, Level2, Level3, Keywords as columns. While selecting, do a union of 3 queries - each grouping keywords at each level. Combine the result and do a final select after removing duplicates. — ArunGeorge, Jul 15 '17 at 10:58

Gordon Linoff · Answer 1 · 2017-07-15T19:16:44.983

0

For the data you have provided, this is a basic string concatenation:

select t.*,
       coalece(t.keywords,
               stuff( (select ', ' + t2.keywords
                       from t2
                       where t2.lvl like t.lvl + '%' and
                             t2.lvl <> t.lvl and
                             t2.keywords is not null
                       for xml path ('')
                      ), 1, 2, ''
                     )
               ) as keywords
from t;

I would caution you, though, that storing keywords in comma-delimited fields is usually a really bad idea. You should re-consider your data structure.

edited Jul 15 '17 at 19:16

answered Jul 15 '17 at 11:20

Gordon Linoff

1,242,037
58
646
786

Thanks, it works, with a slight adjustment: coalece(t.lvl, should be coalesce(t.keywords, Why is it a bad idea to store the keywords in a field comma separated? Would a semi colon ; be a better delimeter? Or is there another reason? Thx, Rob – emphyrio Jul 15 '17 at 16:25

t-sql need a rollup view from hierarchal data

1 Answers1