Give value if field per row has value using sql

542 views mysql
0

I tried for hours and read many posts but I still can't figure out how to handle this request:

I have a table like this:

+------+-----------+
|caseID|groupVarian|
+------+-----------+
|1     |A,B,C,D,E  |
+------+-----------+
|2     |A,B,N,O,P  |
+------+-----------+
|3     |A,B,N,O,P  |
+------+-----------+
|4     |A,B,C,D,F  |
+------+-----------+
|5     |A,B,C,D,E  |
+------+-----------+

I would like to give new colum with variant name if per groupvarian has same value then variant has variant.

The output should be something like:

+------+-----------+----------+
|caseID|groupVarian|namevarian
+------+-----------+----------+
|1     |A,B,C,D,E  |v1        |
+------+-----------+----------+
|2     |A,B,N,O,P  |v2        |
+------+-----------+----------+
|3     |A,B,N,O,P  |v2        |
+------+-----------+----------+
|4     |A,B,C,D,F  |v3        |
+------+-----------+----------+
|5     |A,B,C,D,E  |v1        |
+------+-----------+----------+

answered question

What is your MySQL version ?

@madhur baiya 5.6

1 Answer

0

You could use DENSE_RANK(MySQL 8.0):

SELECT *, CONCAT('v', DENSE_RANK() OVER(ORDER BY groupVarian)) AS namevarian
FROM tab

posted this

Have an answer?

JD

Please login first before posting an answer.