Column in database with multiple values - need to classify
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!
Microsoft SQL ServerSQL
Last Comment
slightwv (䄆 Netminder)
8/22/2022 - Mon
Kent Olsen
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.
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.