当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Transform table structure of SQL query result (pivot)

问题描述:

I have a table coming from a query with the following content structure:

+-----+-----------+-------+

|date | sensor_id | power |

+-----+-----------+-------+

|date1| 1 | 0.51 |

|date1| 2 | 0.52 |

|date1| 3 | 0.53 |

|date2| 1 | 0.61 |

|date2| 2 | 0.61 |

|date2| 3 | 0.63 |

|date3| 1 | 0.71 |

|date3| 2 | 0.72 |

|date3| 3 | 0.73 |

+-----+-----------+-------+

Is there a way to transform this table in to the following by using SQL?

+-----+-----------+------------+-----------+

|date | 1 | 2 | 3 |

+-----+-----------+------------+-----------+

|date1| 0.51 | 0.52 | 0.53 |

|date1| 0.61 | 0.62 | 0.63 |

|date1| 0.71 | 0.72 | 0.73 |

+-----+-----------+------------+-----------+

网友答案:

If you know what the set of possible values for sensor_id is beforehand you can use conditional aggregation to do the pivot:

select 
  date,
  max(case when sensor_id = 1 then power else 0 end) as "1",
  max(case when sensor_id = 2 then power else 0 end) as "2",
  max(case when sensor_id = 3 then power else 0 end) as "3"
from Table1
group by date;

If you don't know all the possible values you can use dynamic sql to build an appropriate statement; the query is specific to MySQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(CASE WHEN sensor_id = ',
      sensor_id,' 
      THEN power ELSE 0 END) AS "', 
      sensor_id,'"'
    )
  ) INTO @sql FROM Table1;

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

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

See this SQL Fiddle for examples.

网友答案:

Yes it is possible and is calles "Pivot" Check this .. Pivot in Mysql

网友答案:
select * 
from table
group by sensor_id
分享给朋友:
您可能感兴趣的文章:
随机阅读: