How to Pivot Rows to Columns in MySQL Without Using Case

Is there a way to pivot rows to columns in MySQL without using CASE?

The link that Kangkan provided will show you how to pull this off had you known the column names in advance. We're going for the same logic, except using dynamic SQL to build the statement. There is 2 parts to each field that you need to include, the field in the select statement and an appropriate join to get the value...so we'll need to build the statement in two parts

First declare 3 variables to build this...I'll go with @select, @join, and @sql for this example. Give the variables the initial values

 set @select = 'select user_id,'
 set @join = 'from table t'

now declare and load a cursor with the distinct values in the table.key field. I'm going to use @field as the variable gets populated with the distinct table.key field. Then loop through it building the two variables:

 set @select = @select + ', ' + @field + '.value as '+@field+'
 set @join = @join + ' left join table ' + @field + 'on '+@field+'.key = t.key and and '+@field+'.user_id = t.user_id

(the join is designed to use the value in @field as the alias of the table)

loop through your cursor building out @select and @join. At the end of the loop:

set @sql = @select + @join + 'where clause if you want'
exec @sql

Dynamic SQL built like this can be an absolute pain to trouble shoot (and get right) and open up security issues...but it's about the only way I can see this accomplished. Watch for size restrictions on your variables....if you have too many distinct Key's there, the variables grow too big. Sorry I can't be more exact with the pseudo on this...you'll find building dynamic sql in sql is painstaking.

Pivot Rows to Column in SQL without Aggregation

You can use conditional aggregation:

select object_key,
       max(case when name = 'OBJECT_NAME' then value end) as object_name,
       max(case when name = 'Start Time' then value end) as start_time,
       max(case when name = 'End Time' then value end) as end_time,
       max(case when name = 'row_count' then value end) as row_count,
       max(case when name = 'Execution Time' then value end) as execution_time
from t
group by object_key;

I'm not sure why you specify "no aggregation" in the question.

If you really have an aversion to aggregation use can use joins:

select ton.object_key, ton.value as object_name,
       ts.value as start_time, te3.value as end_time,
       . . 
from t ton on
     t ts
     on ton.object_key = ts.object_key join
     t te
     on ton.object_key = t3.ojbect_key. join
     . . . ;

Convert rows to column using CASE in mysql

Seems sufficiently similar to me, but anyway...

SELECT ipiresia
     , MAX(CASE WHEN program = 1 THEN 'x' ELSE '' END) program1 
     , MAX(CASE WHEN program = 2 THEN 'x' ELSE '' END) program2 
     , MAX(CASE WHEN program = 3 THEN 'x' ELSE '' END) program3 
  FROM my_table 
 GROUP 
    BY ipiresia;

Personally, I wouldn't do this - preferring where possible to handle issues of data display in application-level code.

Transpose a row into columns with MySQL without using UNIONS?

I got this out of the book The Art of SQL, pages 284-286:

Let's say your table name is foo.

First, create a table called pivot:

CREATE Table pivot (
  count int
);

Insert into that tables as many rows as there are columns that you want to pivot in foo. Since you have three columns in foo that you want to pivot, create three rows in the pivot table:

insert into pivot values (1);
insert into pivot values (2);
insert into pivot values (3);

Now do a Cartesian join between foo and pivot, using a CASE to select the correct column based on the count:

SELECT foo.id, Case pivot.count
  When 1 Then cat
  When 2 Then one_above
  When 3 Then top_level
End Case
FROM foo JOIN pivot;

This should give you what you want.

pivot rows to columns based on condition

The simplest method is to put the values in a single column:

select student_id, group_concat(score order by id)
from t
group by student_id;

That is sufficient for many purposes. If you want separate columns, you need to create a column. One way uses variables:

select student_id,
       max(case when rn = 1 then score end) as score_1,
       max(case when rn = 2 then score end) as score_2,
       max(case when rn = 3 then score end) as score_3,
       max(case when rn = 4 then score end) as score_4
from (select t.*,
             (@rn := if(@s = student_id, @rn + 1,
                        if(@s := student_id, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @s := -1, @rn := 0) params
      order by student_id, id
     ) t
group by student_id;