0

I have a column as below

Products
jeans,oil
jeans,shampoo

I want to split the strings and use it in the same column using SQL. The result I want is 

Products  count
jeans     2
oil       1
shampoo   1

Could you please guide me in getting this result

Thank you

SrihariRaghu
  • 123
  • 2
  • 11
  • Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Mani Deep Jun 21 '18 at 06:31
  • similar answer: https://stackoverflow.com/a/17942691/2750968 – Mani Deep Jun 21 '18 at 07:02

2 Answers2

4

You are storing CSV data in your SQL table, which is not a good thing. But it looks like you are trying to move away from that, which is a good thing. Here is one option using a union with SUBSTRING_INDEX:

SELECT Products, COUNT(*) AS count
FROM
(
    SELECT SUBSTRING_INDEX(Products, ',', 1) AS Products FROM yourTable
    UNION ALL
    SELECT SUBSTRING_INDEX(Products, ',', -1) FROM yourTable
) t
GROUP BY Products
ORDER BY
    count DESC, Products;

enter image description here

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    assuming column "products" has only one comma separated value., ex: jeans,oil this doesn't work if it has three values "jeans,oil,conditioner" – Mani Deep Jun 21 '18 at 06:36
  • Is this using MYSQL ? When I tried this in w3schools SQL, it is showing no such function like substring_index. Could you please tell me if there is an alternative to this substring_index? – SrihariRaghu Jun 21 '18 at 06:37
  • @SrihariRaghu Check the demo, there most certainly is a `SUBSTRING_INDEX`. Maybe your demo sight is using some really archaic version of MySQL which does not have this function. – Tim Biegeleisen Jun 21 '18 at 06:39
  • Thank you for the demo and your answer. ... Yes as ManiDeep said, if I add one more value to the product name, it is taking the first and last index. It is not considering the intermediate values – SrihariRaghu Jun 21 '18 at 06:42
  • To handle arbitrary numbers of CSV data you'll need to write a UDF or a stored proc. This has been handled already in other questions. I only answered based on the data you showed us. _Don't_ store CSV like this if you can avoid it. – Tim Biegeleisen Jun 21 '18 at 06:43
  • Thank you for your suggestion. :) made my day with your demo and answer as well – SrihariRaghu Jun 21 '18 at 06:49
1

Firstly you need to split the data into two columns like

    SELECT CASE
     WHEN name LIKE '%,%' THEN LEFT(name, Charindex(' ', products) - 1)
     ELSE name
   END,
   CASE
     WHEN name LIKE '%,%' THEN RIGHT(name, Charindex(' ', Reverse(products)) - 1)
   END
   FROM   YourTable

then you need to union this with the same table... and the final code will look like...

    select count( distinct abc), abc from 
    (
    SELECT CASE
       WHEN PA_NAME LIKE '% %' THEN LEFT(PA_NAME, Charindex(' ', PA_NAME) - 1)
       ELSE PA_NAME
    END [abc]
    FROM   phparty

    union all

    SELECT CASE
     WHEN PA_NAME LIKE '% %' THEN RIGHT(PA_NAME, Charindex(' ', Reverse(PA_NAME)) -1)
   END [abc]
    FROM   phparty
    ) t group by abc

here you can replace pa_name with your_column_name

Amrita Srivastava
  • 384
  • 1
  • 7