Simple Pivot sample
15,416
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;
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;
Comments
-
Ezi almost 2 years
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.
I attached the data how its stored in SQL server and the output how I want it to be.
Data:
Required Output:
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')
-
YS. over 11 years+1 Aaron - but just curious, are
MAX([Basic Phone])..MAX([Blackberry])
andGROUP BY MasterID
needed ? if yes - why ? -
Adir D over 11 years@YS. because the pivot in this case doesn't collapse to a single row. If you run the inner query alone against the table in the Q, you get three rows instead of one.
-
YS. over 11 yearsThanks Aaron. I asked that because my version is a bit different to yours - I've got the sql fiddle if you or OP is interested: sqlfiddle.com/#!3/cbf94/1
-
Adir D over 11 yearsYS. Ah I see, I specified the pivot columns redundantly. I'll fix it, thanks.