数据存在于行到列 [英] Data present in rows to columns

问题描述

我有一个场景，其中有一个带有记录的表:

I have a scenario, where in, I've a table with records:

AppNo   Location    Department  Code
1       NY          XYZ         1
1       NY          ABC         2
1       NY          PQR         3
2       TX          XYZ         1
2       TX          ABC         2

可以有多行(最多5行)，其中AppNo和Location相同，部门和代码值也不同.

There can be multiple rows(Maximum 5) with same AppNo and Location with different Department and Code values.

我们需要一个以以下方式获取数据的存储过程:

we need a Stored procedure which fetches data in below manner:

AppNo   Location    Department  Code    Location    Department  Code    Location    Department  Code    Location    Department  Code    Location    Department  Code
1       NY          XYZ         1       NY          ABC         2       NY          PQR         3       
2       TX          XYZ         1       TX          ABC         2

基本上，对于每个AppNo，我们都需要在一行中记录数据，并重复各列.

Basically, for each AppNo, we need data in a single row, with the columns repeated.

请向我建议一些实现此目的的方法.

Please suggest me some ways to accomplish this.

预先感谢克里希纳

推荐答案

MySQL中没有PIVOT命令，但是有解决方法-

There is no PIVOT command in MySQL, but there are workarounds - Pivot table basics: rows to columns..., MySQL pivot tables (transform rows to columns).

对于您的情况，您可以使用类似这样的内容-

For your case you could use something like this -

SELECT AppNo,
  MAX(IF(code = 1, Location, NULL)) Location,
  MAX(IF(code = 1, Department, NULL)) Department,
  1 Code1,
  MAX(IF(code = 2, Location, NULL)) Location,
  MAX(IF(code = 2, Department, NULL)) Department,
  2 Code2,
  MAX(IF(code = 3, Location, NULL)) Location,
  MAX(IF(code = 3, Department, NULL)) Department,
  3 Code3,
  MAX(IF(code = 4, Location, NULL)) Location,
  MAX(IF(code = 4, Department, NULL)) Department,
  4 Code4,
  MAX(IF(code = 5, Location, NULL)) Location,
  MAX(IF(code = 5, Department, NULL)) Department,
  5 Code5
FROM <table name>
GROUP BY AppNo

P.S.您可以从存储的例程中运行此查询.

P.S. You can run this query from the stored routine.

这篇关于数据存在于行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！