如何在 MySQL 中将行更改为列 [英] How to change rows to columns in MySQL

问题描述

我有一个查询，它显示了来自数据库中各种表的一些信息.

SELECTc.姓名，jp.PieceType,jp.数量来自客户 cLEFT JOIN job_new jn ON c.JobID = jn.IDLEFT JOIN job_pieces jp ON c.JobID = jp.JobID哪里 c.Company_ID = 123按 c.ID 分组

我在 sqlfiddle 上创建了数据库:

set @sql = null;选择group_concat(不同的连接('max(当 PieceType = ''' 时，件型，''' 然后数量结束) AS ',连接(件型))) 到@sql来自客户 cLEFT JOIN job_new jn ON c.JobID = jn.IDLEFT JOIN job_pieces jp ON c.JobID = jp.JobID哪里 c.Company_ID = 123;set @sql = concat('select c.Name,', @sql, ' FROM customer cLEFT JOIN job_new jn ON c.JobID = jn.IDLEFT JOIN job_pieces jp ON c.JobID = jp.JobID哪里 c.Company_ID = 123按 c.ID 分组');从@sql 准备 stmt；执行stmt；解除分配准备stmt；

I have a query that is displaying some information from various tables in a database.

SELECT    
                c.Name,
                jp.PieceType,
                jp.Qty
        FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.ID

I created the database on sqlfiddle: http://www.sqlfiddle.com/#!9/13230/8

Each company can have many piece types but right now the query only displays one type even though more exist. In the sqlfiddle database you can see company 123 has 3 PieceTypes.

So I want to display the piece types as columns instead of rows. I want the PieceTypes to be displayed in separate columns like this:

解决方案

Hey try this for pivoting of table: SQLFIDDLE

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when PieceType = ''',
      PieceType,
      ''' then Qty end) AS ',
      concat(PieceType)
    )
  ) into @sql
from customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123;

set @sql = concat('select c.Name,', @sql, ' FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.ID
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

这篇关于如何在 MySQL 中将行更改为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！