0

How do get I data from column which contains multiple values for a single record to show in new columns with custom heading, without using string function in MYSQL.

Here is a subset of table which I have pulled using multiple joins. 

Product  | Category
-------- | ------------
MSI60000 | Sunglasses
MSI60000 | Minimal Coding
MSI60001 | Glasses
MSI60001 | Minimal Coding
MSI60002 | Sunglasses
MSI60002 | Short Coding
MSI60003 | Goggles
MSI60003 | Long Coding
MSI60004 | Goggles
MSI60004 | Shortcoded

I'm looking to achieve this from above table.

Product  | Type        | Coding
-------- | ----------- | --------------
MSI60000 | Sunglasses  | Minimal Coding
MSI60001 | Glasses     | Minimal Coding
MSI60002 | Sunglasses  | Short Coding
MSI60003 | Goggles     | Long Coding
MSI60004 | Goggles     | Shortcoded

Solution given here kind of helped me however it uses string functions which I can't use.

Community
  • 1
  • 1
user3859857
  • 3
  • 3

1 Answers1

0

You could use this query -

SELECT
  Product,
  MAX(IF(LOCATE('Coding', Category) = 0, Category, NULL)) AS Type,
  MAX(IF(LOCATE('Coding', Category) > 0, Category, NULL)) AS Coding
FROM
  tbl
GROUP BY
  Product;

MSI60000    Sunglasses      Minimal Coding
MSI60001    Glasses         Minimal Coding
MSI60002    Sunglasses      Short Coding
MSI60003    Goggles         Long Coding

...but there is LOCATE function.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thankyou! I can't target specific string here as column may contain unpredictable strings. Added another row in the end. – user3859857 Feb 27 '17 at 10:38
  • There is no way to understand which is type, which is coding for values like `Goggles | Shortcoded`. Only to compare strings. – Devart Feb 27 '17 at 11:16
  • In some cases it is possible to automate pivot - http://codingsight.com/pivot-tables-in-mysql/ – Devart Mar 01 '17 at 08:29