Convert numbered categories to named categories

user_12933212 0
Viewed 41

I'm sure there must be a simple solution to this. I've got two tables one with Stock Data and another where items category names are saved. So let's imagine the Stock table

ID      |   Name   |   Price    | CategoryID
_____________________________________________
5465814 |Item1     | 2.00       |  1         
5465815 |Item2     | 2.00       |  2         
5465816 |Item3     | 2.00       |  1/1         
5465817 |Item4     | 2.00       |  1/3/5         
5465818 |Item5     | 2.00       |  4            
5465818 |Item5     | 2.00       |  4/1            
5465818 |Item5     | 2.00       |  4/2/7

Then Category table is something like this:

CategoryID|   Name   
_____________________________________________
1         |Tools
1/1       |Manual
1/2       |Electrical
1/3       |Pneumatic
1/3/5     |Saws
2         |Chairs
4         |Toys
4/1       |for Girls
4/1/1     |Dolls
4/2       |for Boys
4/2/1     |Cars
4/2/7     |Action Figures

What i can't figure out is how to convert while making a joined select this ID to a combined category name like converting 4/2/7 to: Toys > for Boys > Action Figures

what i finally wish to accomplish is to get a table like this:

ID      |   Name   |   Price    | Category
_____________________________________________
5465814 |Item1     | 2.00       |  Tools         
5465815 |Item2     | 2.00       |  Chairs         
5465816 |Item3     | 2.00       |  Tools > Manual        
5465817 |Item4     | 2.00       |  Tools > Pneumatic > Saws
5465818 |Item5     | 2.00       |  Toys            
5465818 |Item5     | 2.00       |  Toys > for Girls    
5465818 |Item5     | 2.00       |  Toys > for Boys > Action Figures

This is what i got so far it does work up to 4 nested category names:

CASE    
            WHEN len(sd.CategoriyID) - len(replace(sd.CategoriyID,'/','')) = 0 
                THEN ISNULL((SELECT Name FROM Categories WHERE ID = sd.CategoriyID), '')
            WHEN len(sd.CategoriyID) - len(replace(sd.CategoriyID,'/','')) = 1
                THEN 
                    ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 1) - 1)), '') 
                    + ' > ' 
                    + ISNULL((SELECT Name FROM Categories WHERE ID = sd.CategoriyID), '')
            WHEN len(sd.CategoriyID) - len(replace(sd.CategoriyID,'/','')) = 2
                THEN 
                    ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 2) - 1)), '') 
                    + ' > ' 
                    +ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 1) - 1)), '') 
                    + ' > ' 
                    + ISNULL((SELECT Name FROM Categories WHERE ID = sd.CategoriyID), '')
            WHEN len(sd.CategoriyID) - len(replace(sd.CategoriyID,'/','')) = 2
                THEN 
                    ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 3) - 1)), '') 
                    + ' > ' 
                    +ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 2) - 1)), '') 
                    + ' > ' 
                    +ISNULL((SELECT Name FROM Categories WHERE ID = 
                    SUBSTRING(sd.CategoriyID, 1 , dbo.CHARINDEX2('/', sd.CategoriyID, 1) - 1)), '') 
                    + ' > ' 
                    + ISNULL((SELECT Name FROM Categories WHERE ID = sd.CategoriyID), '')
        END AS Category
sql-server-2014 sql-server sql database
1 Answers

SQL Server doesn't support string agg as a window function for cumulative windows. But, you can use apply instead. So, get the full name from the second table using:

select c.*, v.category
from category c cross apply
     (values (stuff( (select ' > ' + coalesce(c2.name, '')
                      from category c2
                      where c.categoryId + '/' like c2.categoryId + '/%'
                      order by c2.categoryId
                      for xml path (''), type) .value('.', 'nvarchar(max)'
                     ), 1, 3, ''
                   )
             )
     ) v(category);

Here is a db<>fiddle for this portion.

You can then join to this query:

with c as (
      select c.*, v.category
      from category c cross apply
           (values (stuff( (select ' > ' + coalesce(c2.name, '')
                            from category c2
                            where c.categoryId + '/' like c2.categoryId + '/%'
                            order by c2.categoryId
                            for xml path (''), type) .value('.', 'nvarchar(max)'
                           ), 1, 3, ''
                         )
                   )
           ) v(category);
      )
select s.*, c.category
from stock s join
     c
     on s.categoryId = c.categoryId;
user_11440350
Related