MYSQL two columns that have value in first column how can i do an inner join

One way to get the result would be:

SELECT t.dt
     , MAX(IF(t.ticker='A',t.open,NULL)) AS A
     , MAX(IF(t.ticker='B',t.open,NULL)) AS B
  FROM mytable t
 GROUP BY t.dt

(In MySQL the MAX aggregate can actually be omitted, thought an aggregate is required in other DBMS.)

SELECT t.dt
     , IF(t.ticker='A',t.open,NULL) AS A
     , IF(t.ticker='B',t.open,NULL) AS B
  FROM mytable t
 GROUP BY t.dt

Another approach:

SELECT t.dt
     , t.open AS A
  FROM mytable t
  LEFT
  JOIN (SELECT s.dt
             , t.open AS B
          FROM mytable s
         WHERE s.ticker = 'B'
         GROUP BY s.dt
       ) b
    ON b.dt = t.dt
 WHERE t.ticker = 'A'
 GROUP BY t.dt
 ORDER BY t.dt

You need to add Max to your Group_Concat, Try this

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'Max(case when ticker = ''',
      ticker,
      ''' then open end) AS ',
      replace(ticker, ' ', '')
    )
  ) INTO @sql
from prices;

SET @sql = CONCAT('SELECT x.dt, ', @sql, ' from prices x
group by x.dt');

PREPARE stmt FROM @sql;
EXECUTE stmt;

SQL Fiddle Demo

try it

    select a.dt,a.A,CASE WHEN ticker='B' THEN open END AS 'B' from (SELECT dt,CASE WHEN ticker='A' THEN open END AS 'A' FROM test group by dt) a inner join test using(dt) where CASE WHEN ticker='B' THEN open END  is not null;

result

+------+-------------------+------+
| dt   | A                 | B    |
+------+-------------------+------+
|    1 |                 1 |    3 |
|    2 | 1.100000023841858 |  2.5 |
+------+-------------------+------+

Try this:

SELECT GROUP_CONCAT(CONCAT(" MAX(IF(ticker = '", ticker, "', open, NULL)) AS ", ticker)) INTO @sql
FROM prices;

SET @sql = CONCAT('SELECT dt, ', @sql, ' FROM prices GROUP BY dt'); 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Check this SQL FIDDLE DEMO

OUTPUT

| DT |   A |   B |
------------------
|  1 |   1 |   3 |
|  2 | 1.1 | 2.5 |