简单的数据透视样本(Simple Pivot sample)

我需要所有masterid的的一份报告，但它可能只是一个行上。我知道这是一个简单的事，但我不能正确地找出语法。

我重视其如何存储在SQL服务器和输出如何我想它是数据。

数据：

所需的输出：

CREATE TABLE [dbo].[Services]
    ([ServiceID] [int] IDENTITY(1,1) NOT NULL,
    [MasterID] [nvarchar](10) NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [Status] [nvarchar](50) NOT NULL)

Insert Into Services (MasterID, Type , Status) values (123, 'Basic Phone', 'Open')
Insert Into Services (MasterID, Type , Status) values (123, 'BlackBerry', 'Open')
Insert Into Services (MasterID, Type , Status) values (123, 'Pixi', 'Closed')

Answer 1:

SELECT MasterID, 
  [Basic Phone] = MAX([Basic Phone]),
  [Pixi] = MAX([Pixi]),
  [Blackberry] = MAX([Blackberry])
FROM
(
  SELECT MasterID, [Basic Phone],[Pixi],[Blackberry]
  FROM dbo.Services AS s
  PIVOT 
  (
    MAX([Status]) FOR [Type] IN ([Basic Phone],[Blackberry],[Pixi])
  ) AS p
) AS x
GROUP BY MasterID;

或者更简单 - 和信贷@YS。 指出我的冗余。

SELECT MasterID, 
  [Basic Phone],
  [Pixi],
  [Blackberry]
FROM
(
  SELECT MasterID, Status, Type FROM dbo.Services
)
AS s
PIVOT 
(
  MAX([Status]) FOR [Type] IN ([Basic Phone], [Blackberry], [Pixi])
) AS p;

文章来源: Simple Pivot sample