CODEWITHSUNDEEP
mysql
deepak
deepak

Reputation: 55

Get a single column comprised of several values

How do I get a column of items separated by commas in MySQL?

If I do select * from (select 1, 2, 3, 4, 5) a; it gives me 5 different columns, instead of a single column named (1, 2, 3, 4, 5). Can anyone help me out?

Upvotes: 0

Views: 64

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Mike's solution is the better one for static ranges, but here is a MySQL specific solution in case you need the number of rows to be easier to adjust;

SELECT @row := @row + 1 as row 
FROM 
  (SELECT 0 UNION ALL SELECT 1) t2,
  (SELECT 0 UNION ALL SELECT 1) t4,
  (SELECT 0 UNION ALL SELECT 1) t8,
  (SELECT 0 UNION ALL SELECT 1) t16,
  (SELECT @row:=0) a
WHERE @row < 5

This will generate up to 16 rows by just adjusting the WHERE clause, and is easy to expand since one extra select will double the range.

An SQLfiddle to test with.

Upvotes: 0

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95572

This is a standard SQL approach. It doesn't use any proprietary features.

select 1 as n
union all
select 2
union all
select 3
union all
select 4
union all
select 5;

Upvotes: 5

Related Questions