CODEWITHSUNDEEP
mysqlsqlpivot
AriesDevil
AriesDevil

Reputation: 277

How to create extra columns use column value?

I'm having the following problem

I have a table like this

+------+---------+------------+
| imei | channel |  sys_date  |
+------+---------+------------+
| 1111 |unknown01| 2013-04-15 |
| 2222 |unknown02| 2013-04-15 |
| 3333 |unknown03| 2013-04-15 |
| 4444 |unknown01| 2013-04-16 |
| 5555 |unknown02| 2013-04-17 |
| 6666 |unknown02| 2013-04-17 |
+------+---------+------------+

Now I need write a SQL let result like this(let the channel's value as extra column)

+----------+---------+---------+---------+
| sys_date |unknown01|unknown02|unknown03|
+----------+---------+---------+---------+
|2013-04-15|    1    |    1    |    1    |
|2013-04-16|    1    |    0    |    0    |
|2013-04-17|    0    |    2    |    0    |
+----------+---------+---------+---------+

I have no idea about this sql,so i need help ,thanks

Upvotes: 2

Views: 76

Answers (1)

Himanshu
Himanshu

Reputation: 32602

You can use CASE for that

SELECT sys_date
,SUM(CASE channel WHEN 'unknown01' THEN 1 ELSE 0 END) AS unknown01
,SUM(CASE channel WHEN 'unknown02' THEN 1 ELSE 0 END) AS unknown02
,SUM(CASE channel WHEN 'unknown03' THEN 1 ELSE 0 END) AS unknown03
FROM Table1
GROUP BY sys_date

See this SQLFiddle

If you don't know the number of channels you can use this dynamic query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when channel = ''',
      channel,
      ''' then 1 else 0 end) AS ',
      channel
    )
  ) INTO @sql
FROM Table1;


SET @sql = CONCAT('SELECT sys_date, ', @sql, '
                  FROM Table1 
                  GROUP BY sys_date');

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

See this SQLFiddle

Upvotes: 1

Related Questions