以mysql数据库为例:
原表结构:
①行转列(sql语句):
SELECT xsy,
sum( CASE WHEN xscp = ‘牛奶’ THEN xsl ELSE 0 END ) AS 牛奶,
sum( CASE WHEN xscp = ‘苹果汁’ THEN xsl ELSE 0 END ) AS 苹果汁,
sum( CASE WHEN xscp = ‘柳橙汁’ THEN xsl ELSE 0 END ) AS 柳橙汁,
sum( CASE WHEN xscp = ‘巧克力’ THEN xsl ELSE 0 END ) AS 巧克力,
sum( CASE WHEN xscp = ‘牛肉干’ THEN xsl ELSE 0 END ) AS 牛肉干 from xl group by xsy
效果展示(如图):
②接下来如果你想把上面转化后的表(xlx)在转化回去就是列转化为行了,主要运用union实现,sql语句如下:
SELECT xsy,‘牛奶’ AS xscp,牛奶 AS xsl FROM xlx UNION
SELECT xsy,‘苹果汁’ AS xscp,苹果汁 AS xsl FROM xlx UNION
SELECT xsy,‘柳橙汁’ AS xscp,柳橙汁 AS xsl FROM xlx UNION
SELECT xsy,‘巧克力’ AS xscp,巧克力 AS xsl FROM xlx UNION
SELECT xsy,‘牛肉干’ AS xscp,牛肉干 AS xsl FROM xlx GROUP BY xsy
效果展示(如图):
以上是最简单的两种行列之间的转换。