利用PIVOT将竖表转横表（SQL Server2008+）

1、需求示意图

2、创建表结构及数据

IF OBJECT_ID('dbo.OpenSchema') IS NOT NULL DROP TABLE dbo.OpenSchema;

CREATE TABLE dbo.OpenSchema
(
  objectid  INT          NOT NULL,
  attribute NVARCHAR(30) NOT NULL,
  value     SQL_VARIANT  NOT NULL, 
  PRIMARY KEY (objectid, attribute)
);
GO

INSERT INTO dbo.OpenSchema(objectid, attribute, value) VALUES
  (1, N'attr1', CAST(CAST('ABC'      AS VARCHAR(10))   AS SQL_VARIANT)),
  (1, N'attr2', CAST(CAST(10         AS INT)           AS SQL_VARIANT)),
  (1, N'attr3', CAST(CAST('20070101' AS SMALLDATETIME) AS SQL_VARIANT)),
  (2, N'attr2', CAST(CAST(12         AS INT)           AS SQL_VARIANT)),
  (2, N'attr3', CAST(CAST('20090101' AS SMALLDATETIME) AS SQL_VARIANT)),
  (2, N'attr4', CAST(CAST('Y'        AS CHAR(1))       AS SQL_VARIANT)),
  (2, N'attr5', CAST(CAST(13.7       AS NUMERIC(9,3))  AS SQL_VARIANT)),
  (3, N'attr1', CAST(CAST('XYZ'      AS VARCHAR(10))   AS SQL_VARIANT)),
  (3, N'attr2', CAST(CAST(20         AS INT)           AS SQL_VARIANT)),
  (3, N'attr3', CAST(CAST('20080101' AS SMALLDATETIME) AS SQL_VARIANT));

-- show the contents of the table
SELECT * FROM dbo.OpenSchema;
GO

3、解决方案：

     我们很快会想到用Case When语句来转换后在Max此项，SQL如下：

SELECT objectid,
  MAX(CASE WHEN attribute = 'attr1' THEN value END) AS attr1,
  MAX(CASE WHEN attribute = 'attr2' THEN value END) AS attr2,
  MAX(CASE WHEN attribute = 'attr3' THEN value END) AS attr3,
  MAX(CASE WHEN attribute = 'attr4' THEN value END) AS attr4,
  MAX(CASE WHEN attribute = 'attr5' THEN value END) AS attr5
FROM dbo.OpenSchema
GROUP BY objectid;

很好，这样也能解决问题。

    我们还可以利用PIVOT来将竖表转换为横表，具体如下：

-- Pivoting Attributes, using PIVOT operator
SELECT objectid, attr1, attr2, attr3, attr4, attr5
FROM dbo.OpenSchema
  PIVOT(MAX(value) FOR attribute
    IN([attr1],[attr2],[attr3],[attr4],[attr5])) AS P;

PIVOT具体语法请baidu。

    当然如下SQL也可以解决问题：

-- PIVOT operator, using table expression
SELECT objectid, attr1, attr2, attr3, attr4, attr5
FROM (SELECT objectid, attribute, value FROM dbo.OpenSchema) AS D
  PIVOT(MAX(value) FOR attribute
    IN([attr1],[attr2],[attr3],[attr4],[attr5])) AS P;

结果：

objectid	attr1	attr2	attr3	attr4	attr5
1	ABC	10	2007-01-01 00:00:00.000	NULL	NULL
2	NULL	12	2009-01-01 00:00:00.000	Y	13.700
3	XYZ	20	2008-01-01 00:00:00.000	NULL	NULL