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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
Working example here:
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=097cb75723592e56ee993c7247624330
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
Working example here:
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=097cb75723592e56ee993c7247624330
Premium Content
You need an Expert Office subscription to comment.Start Free Trial