1

We have the query which is given below and the output is as follows:

mysql> select count(1) as cnt ,card.Program_ID program_id ,fd.load_type load_type ,
    -> if(fd.load_type =0 ,concat('unload_cl_proram_',card.program_id) ,concat('load_cl_proram_',card.program_id)) as load_desc
    -> from transaction_log_details fd inner join card card
    -> on fd.Card_ID = card.Card_ID where card.Date_Created>='2011-01-01' and card.Date_Created<='2011-06-01'
    -> and card.Program_ID in (4,5,6,7,8)
    -> group by card.Program_ID,fd.load_type;
+-----+------------+-----------+--------------------+
| cnt | program_id | load_type | load_desc          |
+-----+------------+-----------+--------------------+
|   5 |          4 | 0         | unload_cl_proram_4 |
|  14 |          4 | 1         | load_cl_proram_4   |
|  55 |          5 | 0         | unload_cl_proram_5 |
|  91 |          5 | 1         | load_cl_proram_5   |
|  13 |          6 | 0         | unload_cl_proram_6 |
|  29 |          6 | 1         | load_cl_proram_6   |
| 175 |          7 | 0         | unload_cl_proram_7 |
| 411 |          7 | 1         | load_cl_proram_7   |
|  61 |          8 | 0         | unload_cl_proram_8 |
| 161 |          8 | 1         | load_cl_proram_8   |
+-----+------------+-----------+--------------------+
10 rows in set (0.00 sec)


Now we want the output as follows: (some thing like pivot).
unload_cl_proram_4  load_cl_proram_4    unload_cl_proram_5  load_cl_proram_5    unload_cl_proram_6  load_cl_proram_6    unload_cl_proram_7  load_cl_proram_7    unload_cl_proram_8  load_cl_proram_8    
5               14          55          91          13          29        175   411 61  161
Devin M
  • 9,636
  • 2
  • 33
  • 46
user662253
  • 39
  • 1
  • 1
  • 4
  • 1
    Can you please add code tags around the tabular data? – Devin M May 26 '11 at 05:21
  • 1
    Note you can [format lines as code](http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) by indenting them four spaces. The "{}" button in the editor toolbar does this for you. Edit your question and try it out. Click the orange question mark in the editor toolbar for more information and tips on formatting. For output, make use of `
    ` and `
    ` elements, until such time as SO supports [``](http://meta.stackexchange.com/questions/1777/what-html-tags-are-allowed-on-stack-overflow-server-fault-and-super-user).
     – outis May 26 '11 at 05:31
  • 1
    Also, give yourself a [meaningful username](http://tinyurl.com/so-hints). It shows you're committed and lets others address [replies](http://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) to you using '@'. – outis May 26 '11 at 05:39
  • Note that SQL statements should be code, which means leaving out the MySQL prompts. Edit your question and try it. – outis May 26 '11 at 05:57

1 Answers1

1

Use

CASE WHEN

statements

References -

Similar Question - SQL query to pivot a column using CASE WHEN

http://www.sqlshare.com/pivot-using-the-case-statement_524.aspx (video)

Community
  • 1
  • 1
Raj
  • 22,346
  • 14
  • 99
  • 142