转换此数据维度的SQL查询

Question

是否有简单的SQL查询来转换以下数据？

日期-项目成本

10/31/2009 -a-1

10/31/2009 -b-2

10/31/2009-C-3

2009年10月31日-d-4 11/30/2009 -a-5

11/30/2009 -b-6

11/30/2009-C-7

11/30/2009d-8

在没有任何应用逻辑/存储过程/报告工具的情况下转换成以下报告格式？

项目-10/31/2009-11/30/2009

A-1-5

B-2-6

C-3-7

D-4-8

抱歉，想不出更好的标题..。

Answer 1

SELECT
  item,
  MAX(CASE WHEN dt = CAST('2009-10-31' AS DATE) THEN cost END) AS oct2009_cost,
  MAX(CASE WHEN dt = CAST('2009-11-30' AS DATE) THEN cost END) AS nov2009_cost,
  MAX(CASE WHEN dt = CAST('2009-12-31' AS DATE) THEN cost END) AS dec2009_cost
FROM items
GROUP BY
  item;