Convert Row to Column

问题:

How can I convert this table:-

INV   DESCRIPTION   AMOUNT
--------------------------
1001  CHARGES       100
1001  FREIGHT       30
1001  INSURANCE     20
1002  CHARGES       215
1002  FREIGHT       32
1002  INSURANCE     25

to this format using SQL:-

INV   CHARGES  FREIGHT  INSURANCE
---------------------------------
1001  100      30       20
1002  215      32       25

回答1:

Use:

  SELECT t.inv,
         MAX(CASE WHEN t.description = 'CHARGES' THEN t.amount ELSE NULL END) AS charges,
         MAX(CASE WHEN t.description = 'FREIGHT' THEN t.amount ELSE NULL END) AS freight,
         MAX(CASE WHEN t.description = 'INSURANCE' THEN t.amount ELSE NULL END) AS insurance
    FROM YOUR_TABLE t
GROUP BY t.inv
ORDER BY t.inv

In order to support a dynamic list of descriptions, you'd have to specify which database this is for because the dynamic SQL syntax is different for each one.

PIVOT/UNPIVOT is ANSI syntax, but support is limited:

• SQL Server 2005+
• Oracle 11g+

MySQL doesn't support PIVOT, nor does SQLite. I don't know when/if PostgreSQL or DB2 does...