如何在SQL中透视数据 [英] How to pivot data in SQL

问题描述

我有看起来像这样的数据(3列数据)，很遗憾，我无法使其正确显示

I have data that looks much like this (3 columns of data), unfortunately I can't get it to display properly

NCR NO  LU_NAME           KEY_REF
100001  Project           PROJECT_ID=ID#^
100001  SupplierInfo      SUPPLIER_ID=UNIQUESUPPLIERNUMBER^
100001  PurchaseOrder     ORDER_NO=UNIQUEORDERNO^
100196  PurchaseReceipt   UNIQUE PURCHASE RECEIPT
100511  InventoryPart     CONTRACT=UNIQUECONTRACTNO

我想要的是每个NCR number都有一条记录，而Project，SupplierInfo等的数据列则包含唯一的Key_Ref.假设表名称为OC.有人可以协助执行此操作吗?

What I want is to have one record for each NCR number and a column of data for Project, SupplierInfo, etc, which contains the unique Key_Ref. Let's say the table name is OC. Can someone assist with the code to do this?

推荐答案

这种数据转换类型称为 pivot ，某些数据库产品具有可以为您完成此操作的功能.

This type of data transformation is called a pivot, some database products have a function that can do this for you.

如果在没有枢轴函数的数据库中工作，则可以使用带有CASE表达式的聚合函数:

If you are working in a database that does not have a pivot function, then you can use an aggregate function with a CASE expression:

select ncr_no,
  max(case when LU_NAME = 'Project' then KEY_REF end) Project,
  max(case when LU_NAME = 'SupplierInfo' then KEY_REF end) SupplierInfo,
  max(case when LU_NAME = 'PurchaseOrder' then KEY_REF end) PurchaseOrder,
  max(case when LU_NAME = 'PurchaseReceipt' then KEY_REF end) PurchaseReceipt,
  max(case when LU_NAME = 'InventoryPart' then KEY_REF end) InventoryPart
from yourtable
group by ncr_no

请参见带有演示的SQL小提琴.

上面的方法在已知数量或有限数量的LU_NAME值的情况下都可以很好地工作，如果您拥有未知的数量，则需要实现动态SQL，但是代码会因数据库而异.

The above will work great with a known number or finite number of LU_NAME values, if you will have an unknown number then you will need to implement dynamic SQL but that code will vary depending on your database.

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