-1

I have a single column in my table (table name is rtp2k) that contains multiple values separated by a comma. I would like to split these values and then list them by number.

My column currently looks like: 

+-------+
| A2    | 
| a,b,c | 
| d,e,f |
| c,b   |
| a,d,f |
| d,e,f |
+-------+

I know this is not best practice. I did not create this table. I am just supposed to pull data from it to make a chart using mysqli, php and google charts by a deadline.

My current query looks like this, but I don't need the id in there and I would like to count them by value instead of listing each individually. 

select
  rtp2k.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(rtp2k.a2, ',', numbers.n), ',', -1) a2
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN rtp2k
  on CHAR_LENGTH(rtp2k.a2)
     -CHAR_LENGTH(REPLACE(rtp2k.a2, ',', ''))>=numbers.n-1
order by
  id, n

I would like the query to return something like:

a - 2 
b - 2 
c - 2 
d - 3
e - 2
f - 3

basically separating the string by value and listing how many of each there is.

My current result is: this table

I would be very grateful for any assistance.

Dharman
  • 30,962
  • 25
  • 85
  • 135
S Malfoy
  • 23
  • 1
  • 8
  • what problem you getting? – Mohit Kumar Jun 27 '19 at 01:07
  • This question (even the title) is obviously 2 obvious basic faqs. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. [What is the opposite of GROUP_CONCAT in MySQL?](https://stackoverflow.com/q/17308669/3404097) [How to count occurrences of a column value efficiently in SQL?](https://stackoverflow.com/q/1503959/3404097) – philipxy Jul 07 '19 at 20:11
  • @Strawberry This question is 2 steps that are obvious basic duplicates (see my last comment) but not of the question you give. – philipxy Jul 07 '19 at 20:13

1 Answers1

1
SELECT ColumnID, Column1, value  --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample  
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values

So we were having this: tbl_Sample :

ColumnID|   Column1 |   Tags
--------|-----------|-------------
1       |   ABC     |   a,b,c    
2       |   PQR     |   d,e,f

After running this query:

ColumnID|   Column1 |   value
--------|-----------|-----------
1       |   ABC     |   a
1       |   ABC     |   b
1       |   ABC     |   c
2       |   PQR     |   d
2       |   PQR     |   e
2       |   PQR     |   f
Mohit Kumar
  • 952
  • 2
  • 7
  • 18