How to pivot data in SQL

问题:

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

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?

回答1:

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

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

See SQL Fiddle with Demo.

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.