0

How to convert columns of one row into one row each?

Having data in table as below:

+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
|  a1  |  a2  |  a3  |  a4  |
+------+------+------+------+

required output: all values in the row should go in single column as below.

+---------------+
|   All_cols    |
+---------------+
|  a1,a2,a3,a4  |
+---------------+

Like in image: Here mytable always have one record, so I need the output as below.

enter image description here

want to avoid below sql as it requires multiple joins:

select a1.name from  accounts a1 where a1.id='658f3b73-8260-5a7a-ae7e-54c25deded36'

union

Select a2.name from accounts a1
left join accounts a2 on a1.id=a2.parent_id where a1.id='658f3b73-8260-5a7a-ae7e-54c25deded36'

union

Select a3.name from accounts a1
left join accounts a2 on a1.id=a2.parent_id
left join accounts a3 on a2.id=a3.parent_id where a1.id='658f3b73-8260-5a7a-ae7e-54c25deded36'

union

Select a4.name from accounts a1
left join accounts a2 on a1.id=a2.parent_id
left join accounts a3 on a2.id=a3.parent_id
left join accounts a4 on a3.id=a4.parent_id where a1.id='658f3b73-8260-5a7a-ae7e-54c25deded36'

union

Select a5.name from accounts a1
left join accounts a2 on a1.id=a2.parent_id
left join accounts a3 on a2.id=a3.parent_id
left join accounts a4 on a3.id=a4.parent_id
left join accounts a5 on a4.id=a5.parent_id where a1.id='658f3b73-8260-5a7a-ae7e-54c25deded36'
Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
Sam
  • 21
  • 4
  • Dear, It is converting row data into single column value as in image, thank you for your response. – Sam Mar 04 '15 at 11:10
  • dear I just corrected the image as my table always have one record. – Sam Mar 04 '15 at 12:01

1 Answers1

0

You could make a UNION on four single calls

SELECT col1 FROM mytable
UNION
SELECT col2 FROM mytable
UNION
SELECT col3 FROM mytable
UNION
SELECT col4 FROM mytable

will output

enter image description here

You say that

mytable always have one record.

Be aware that if you have more than one record, it's wise to use the same WHERE condition on each SELECT statement above.

Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
  • it works, is there any simple way, because the values are not coming from one table it requires multiple joins, adding my exact sql. – Sam Mar 04 '15 at 14:11