SQL product many catgeorys

Question

Using SQL server 2012 and office 365 excel; I have the following example data in a table:

Sample:

pd_code, height, width, length, pd_catdesc, Category
derp   , 1     , 1    , 1     , derp desc , cattop>>catmain>>catsub___18

the total rows when i select from my table is 10976, if i take out category and group by all other columns i get 10691 (correct unique parts).

Reason

A product can have more than 1 category. The issue I face is for an import. A product has a category defined like...

category___18;

If I want to add multi i need to simply add a semi colon

category___18; category___20;

From the data as you can see my category field is actually the tree, i have some excel code that rids me of all but the sub category. Excel code

=TRIM(RIGHT(SUBSTITUTE(CR3,">>",REPT(" ",250)),250))

category___18;

What I need

what I need to do to the data is to filter out those multi category products. so I can do a bulk import of single category products.

then I need to do a new query that somehow will take all the sub categorys for a product and create a string like

category___18;category___20;

Summary

So I need to only return unique rows based on pd_code but contain a column that will display a subcategory like

cat___18;

And any other categorys like

cat___18;cat___19;

Further Clarification

I need an SQL query that will produce a new column called "categoryrefined" for example, this query needs to look to see if 2 rows are identical (except for category field) then combine category into the new column as explained. (please note the trim method in excel example provided)

Even more clarification and data for a specific user:

The following data is a quick select * from statment that filters 1 pd_code (product) from the table.

|pd_code | Height | width | length |  pd_catDesc                             | category     
+----------------------------------------------------------------------------     
| TC TSPM|     67 |  3000 |     76 | Traditional Cornice Pumice Plain Shaker | Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1959 |     
| TC TSPM|     67 |  3000 |     76 | Traditional Cornice Pumice Plain Shaker | Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1967 |     
| TC TSPM|     67 |  3000 |     76 | Traditional Cornice Pumice Plain Shaker | Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1968 |

So as you can see above 3 rows same code different categorys. I need to make this into one row like

| TC TSPM|     67 |  3000 |     76 | Traditional Cornice Pumice Plain Shaker | Cornice___1959;Cornice___1967;Cornice___1968;

At this point im not sure how much more i can clarify this question any further. If you require additional information I will try and provide.

Check this answer maybe some of those is what you are looking for. **A)** http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns **B)** http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Juan Carlos Oropeza, Nov 08 '16 at 14:39
So you want `GROUP BY pd_code` and concatenate the last category? — Juan Carlos Oropeza, Nov 08 '16 at 15:07
Pretty much yes, only quirk is (excel reference) my category field contains the full Hierachy, i need to trim the result first then concat the result. — lemunk, Nov 08 '16 at 15:08

Juan Carlos Oropeza · Accepted Answer · 2016-11-08T15:40:25.737

1

First attempt Trim Part:

SQL DEMO

SELECT [pd_code], [Height], [width], [length], [pd_catDesc],
       REVERSE(LEFT(reverse(category), charIndex(' >>', reverse(category)) -1 )) as [category]
FROM PRODUCT;

OUTPUT

WITH GROUP_CONCAT:

WITH trim_cat as (
    SELECT [pd_code], [Height], [width], [length], [pd_catDesc],
           REVERSE(LEFT(reverse(category), charIndex(' >>', reverse(category)) -1 )) as [category]
    FROM PRODUCT
)
SELECT DISTINCT
      [pd_code], [Height], [width], [length], [pd_catDesc],
      [category] = (
          SELECT tc.[category] + ' ; '
          FROM trim_cat tc
          WHERE tc.[pd_code] = t.[pd_code]          
          FOR XML PATH('')          
          )
FROM trim_cat t

edited Nov 08 '16 at 15:40

answered Nov 08 '16 at 15:25

Juan Carlos Oropeza

47,252
12
78
118

just checked the results yes that seems to do the same as the excel example nice one. Yes next process would be to group the results and concat the dup results with the trimmed categorys seperated by semi colon – lemunk Nov 08 '16 at 15:30
Excellent just tried it and seems to be exactly what i need, many thanks for that! do you mind if we delete the page of crappy comments ? – lemunk Nov 08 '16 at 15:40
Check again, I forget include the `DISTINCT` – Juan Carlos Oropeza Nov 08 '16 at 15:40
Distinct throws "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." – lemunk Nov 08 '16 at 15:42
I try to sort by category, but couldnt, but because that wasnt a requirement I just remove the sort. If that is a requirement, will need to do more research. – Juan Carlos Oropeza Nov 08 '16 at 15:44
No worrys, sort is not a req, thanks for the help on this. – lemunk Nov 08 '16 at 15:45
if you need it just change it to `ORDER BY [category] FOR XML PATH('') ` – Juan Carlos Oropeza Nov 08 '16 at 15:55

SQL product many catgeorys

1 Answers1