4

The following query is working as expected.

But how do I get the results in rows those are displayed in columns?

select curdate() AS one, 
date_sub(curdate(), interval 15 day) AS two
, date_sub(curdate(), interval 30 day) AS three
, date_sub(curdate(), interval 45 day) AS four
, date_sub(curdate(), interval 60 day) AS five
, date_sub(curdate(), interval 75 day) AS six
, date_sub(curdate(), interval 90 day) AS seven
;

| one        | two        | three      | four       | five       | six        | seven      |
+------------+------------+------------+------------+------------+------------+------------+
| 2010-09-27 | 2010-09-12 | 2010-08-28 | 2010-08-13 | 2010-07-29 | 2010-07-14 | 2010-06-29 |

Expected results in a row:

one 2010-09-27
two 2010-09-12
three 2010-08-28
four 2010-08-13
five 2010-07-29
six 2010-07-14
seven 2010-06-29
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
shantanuo
  • 31,689
  • 78
  • 245
  • 403

1 Answers1

7

You can use UNION ALL for this

select 'one' as label,  curdate() as val
UNION ALL
select 'two' as label,  date_sub(curdate(), interval 15 day) as val
UNION ALL
select 'three' as label,  date_sub(curdate(), interval 30 day)  as val
UNION ALL
select 'four' as label,  date_sub(curdate(), interval 45 day) as val
UNION ALL
select 'five' as label,  date_sub(curdate(), interval 60 day) as val
UNION ALL
select 'six' as label,  date_sub(curdate(), interval 75 day)  as val
UNION ALL
select 'seven' as label,  date_sub(curdate(), interval 90 day) as val
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I think this works only if your columns are the same datatype. I get this error : 'Conversion failed when converting the varchar value 'fieldx' to data type int.' – Misi Oct 02 '13 at 07:19
  • 1
    @Misi You would need to cast the numeric column to string. – Martin Smith Oct 02 '13 at 07:21
  • Do you have a SP that does this ? because I need to extract data dynamic knowing only the table name. And how can I transpose if I have multiple rows ? – Misi Oct 02 '13 at 07:30
  • @Misi - Sounds like you should ask a new question about this. – Martin Smith Oct 02 '13 at 09:58