Simple Pivot sample

Question

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:

Data

Required Output:

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')

I have no idea how MasterID changes from FRIEYIK to 123. Instead of screen shots, could you post real table structure (CREATE TABLE) and real data (e.g. INSERT)? — Aaron Bertrand, Aug 29 '12 at 00:07
123 is just a sample masterid, I included in the question the create and insert — Ezi, Aug 29 '12 at 00:11
no relationship.. It was just sample data. anyways I got the answer. thanks — Ezi, Aug 29 '12 at 00:25

Aaron Bertrand · Accepted Answer · 2012-08-29T01:33:26.950

6

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;

edited Aug 29 '12 at 01:33

answered Aug 29 '12 at 00:20

Aaron Bertrand

272,866
37
466
490

1

+1 Aaron - but just curious, are `MAX([Basic Phone])..MAX([Blackberry])` and `GROUP BY MasterID` needed ? if yes - why ? – YS. Aug 29 '12 at 00:30
@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. – Aaron Bertrand Aug 29 '12 at 00:43
2

Thanks 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: http://sqlfiddle.com/#!3/cbf94/1 – YS. Aug 29 '12 at 01:12
YS. Ah I see, I specified the pivot columns redundantly. I'll fix it, thanks. – Aaron Bertrand Aug 29 '12 at 01:29

Simple Pivot sample

1 Answers1

Linked