简单数据透视示例 [英] Simple Pivot sample
本文介绍了简单数据透视示例的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一份所有masterid的报告,但可能仅是一个连续的报告..我知道这很简单,但是我无法正确找出语法.
I need the a report of all masterid's but it may only be one on a row.. I know that's a simple thing to do but I can't figure out the syntax correctly.
我附加了数据如何存储在SQL Server中以及输出如何存储.
I attached the data how its stored in SQL server and the output how I want it to be.
数据:
必需的输出:
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')
推荐答案
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.指出我的冗余.
Or more simply - and credit to @YS. for pointing out my redundancy.
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;
这篇关于简单数据透视示例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文