0

Table looks like this 

component    maincomponent    subcomponent
5000         5001             5010  
5000         5001             5011  
5000         5002             5015

i want to display the result by using mysql query, or procedure, or function like this. the data will be displayed in the html table so first row of the table will be: So every component, main and sub components will be displayed under the component.

Components:
5000
5001  
5010  
5011  
5002  
5015
Muhabutti
  • 1,256
  • 2
  • 15
  • 20
  • I don't understand what you want. Your sample output just has one column, not 3. – Barmar Nov 03 '16 at 20:33
  • If I understand what you're trying to do, it's probably better done in whatever language you use to display the results, rather than in SQL. – Barmar Nov 03 '16 at 20:35
  • in the first row i want to group fruits, second row group apple, third row sweet, than mango and than verysweet. i am using php and this is a table in which i can insert same values in col1 , col2 but col3 will be different. advice me if i want to do it in php – Muhabutti Nov 03 '16 at 21:10
  • I dont want to query the database many times, i want to get the data once and display it on the view. – Muhabutti Nov 03 '16 at 21:12
  • Query the database with `ORDER BY col1, col2, col3`. Then in the script that displays the results, show each column whenever it changes from the previous row. – Barmar Nov 03 '16 at 21:13
  • See http://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 for how to do it in PHP. The same general approach can be used in any language. – Barmar Nov 03 '16 at 21:15
  • actually the link what you have given is some thing else i want to display the component, maincomponent, subcomponent. Componenet can be same because it is has category, and maincompnent can be duplicate but subcomponent will be different. component , maincomponent, subcomponent. – Muhabutti Nov 03 '16 at 21:36
  • should i create one to many tables or single table. – Muhabutti Nov 03 '16 at 21:38
  • I have got the answer what i was looking for SELECT component, GROUP_CONCAT(DISTINCT maincomponent,'/n',subcomponent,'/n',text order by component) FROM `diagnoses` GROUP BY component – Muhabutti Nov 03 '16 at 22:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127336/discussion-between-muhabutti-and-barmar). – Muhabutti Nov 03 '16 at 22:41

1 Answers1

0

You're looking for UNION operator. Using the data you've provided, you can do the following to achieve the desired results:

SELECT col1 FROM fruit
UNION
SELECT col2 FROM fruit
UNION
SELECT col3 FROM fruit

This will return the following:

fruit
apple
mango
sweet
notsweet
verysweet
JasonJensenDev
  • 2,377
  • 21
  • 30
  • This isn't the right order of results. `apple` should be followed by both of its `col3` values, and `mango` should be followed by its `col3` value. – Barmar Nov 03 '16 at 21:14
  • If ordering is important, I agree with Barmar's comment above; Query the database and then use PHP to display things properly. – JasonJensenDev Nov 03 '16 at 22:42
  • "SELECT component,maincomponent, GROUP_CONCAT(subcomponent,',',text order by component) AS st FROM diagnoses GROUP BY component") Than in PHP $string = (implode(',',(array)$c->st)); $val = (explode(',',$string)); foreach($val as $value){ echo '
    '.$value.'
    '; }     – Muhabutti Nov 05 '16 at 12:09