Column in database with multiple values - need to classify

VINCENT DICIGLIO
VINCENT DICIGLIO used Ask the Experts™
on
Hello all,

I have a database called 'Customer' within it I have a table called 'Product' and within this product table I have a column called 'Class' and ' Client'. Now this class column has single values and  numerous values in the column separated by a ; it looks like this:

Class      Client
1                   a
5                   b
6                   c
8;4                   d
7                   e
2                   f
1                   g
3                   h
2;1                   i
8;5;9;10       j
10                   k



what i am trying to accomplish is separate all of the classes  by client and apply a text value to the class. for example here is the text value I want to correspond with the numerical values under the class column. (unfortunately these text values are not in the database and I dont have permission to create a table).
Class      Poduct
1                cat
2                dog
3                 bird
4                fish
5                 tree
6                car
7                desk
8                TV
9               door
10               bike
11               radio
12               disk

This is what the desired output would look like.

Client      Class
a                cat
b                tree
c               car
d                TV
d                fish
e                desk
f                dog
g                 cat
h                bird
i                dog
i                cat
j                tree
j                TV
j                 door
j                bike
k                 bike

How would I do this? I am using Microsoft SQL Server Management Studio

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect

Commented:
You've got two real issues, both which solve easily.  :)

You don't need to create a table.  Use a CTE to build a "Local" table and if a matching table is ever created as a permanent table, just delete the definition from the CTE and the SQL still works.

WITH classes () AS
(
  SELECT 1 as class,  'cat" as product
  UNION ALL
  SELECT 2, dog
  UNION ALL
  SELECT 3, bird
...
)

Parsing the string to return each class can be done with recursive SQL, a function, regex, etc.  Do you have a preference?

Then it's just a matter of writing the nearly trivial SQL to put it all together.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If you are using SQL Server 2016 and have  the compatibility level set at least 130, you have a STRING_SPLIT function that can replace the XML trick below:
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

Since I don't have access to that, I'm using the XML trick.

with classes as (
	select '1' as class,'cat' as product
	union all
	select '2','dog'
	union all
	select '3','bird'
	union all
	select '4','fish'
	union all
	select '5','tree'
	union all
	select '6','car'
	union all
	select '7','desk'
	union all
	select '8','TV'
	union all
	select '9','door'
	union all
	select '10','bike'
	union all
	select '11','radio'
	union all
	select '12','disk'
),
csv_to_rows as (
	select client,Split.a.value('.', 'VARCHAR(100)') as class
	from  
	(
     	select client, CAST ('<M>' + REPLACE(class, ';', '</M><M>') + '</M>' as xml) as data 
     	from product     
	) as a 
	cross apply data.nodes ('/M') as split(a)
)
select client, product
from csv_to_rows r
	join classes c on  r.class=c.class

Open in new window


Working example here:
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=097cb75723592e56ee993c7247624330

Author

Commented:
Thank you so much for the help!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Happy to help and welcome to the site!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial