0

I am trying to convert rows to columns . Please look at the below sample:

colname  |  data1 |   data2

---------|--------|------------------------------------
     a   |    1   |
     a   |    2   |
     a   |    3   |
     b   |    4   |
     b   |    5   |
     c   |        |     6  
     c   |        |     7

I want the output as:

      a  |   b     |   c

---------|--------|------------------------------------
     1   |        |
     2   |        |
     3   |        |
         |    4   |
         |    5   |
         |        |     6  
         |        |     7

Could anyone please share your ideas on how to solve this?

Sudha Eswaran
  • 3
  • 1
  • Please show what have you tried so far?? – Saurabh Sep 26 '16 at 09:46
  • 3
    I'm not sure this can be done without a `PIVOT` unless you use a lot of hardcoding that will break as soon as your data changes. Why can you not use a `PIVOT`? – iamdave Sep 26 '16 at 09:50
  • If you just merely avoiding SUM just because your data type is in "string", you could still use PIVOT, here's a nice article, http://sqlmag.com/t-sql/pivoting-without-aggregation. – Irawan Soetomo Sep 26 '16 at 10:01
  • Are you using MySQL or MS SQL Server? – jarlh Sep 26 '16 at 10:02
  • I guess you are using mysql and that is why you cannot use the mssql `pivot`. Have a look at e.g. [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – Solarflare Sep 26 '16 at 10:05

1 Answers1

2

This isn't really putting rows to columns. So it's not a PIVOT that's needed.
You just want to show values in some columns depending on what value colname has.
A CASE WHEN can be used for that. 

select 
case when colname = 'a' then data1 end as a,
case when colname = 'b' then data1 end as b,
case when colname = 'c' then data2 end as c
from yourtable;
LukStorms
  • 28,916
  • 5
  • 31
  • 45