Cross tabular Type Data With No SUM

• Todd Young-359443

  Old Hand

  Points: 345

  More actions

  August 8, 2011 at 8:14 am

  #100407

  I'm looking for the most efficient way of pulling data in a cross tabular format

  but with no sums. Working with a large existing database, I have a table with

  phone numbers I need to extract from for a report. I could build a large

  temp table, populate it with each type of phone number (requiring multiple queries),

  or reference the table in the from clause using three queries (still essentially temp tables)

  and pull from that but I was wondering if someone could offer a better solution.

  Table and Data:

  CREATE TABLE [dbo].[MemberPhone2] (

  [MemberPhoneId] [int] IDENTITY (1, 1) NOT NULL ,

  [MemberId] [int] NOT NULL ,

  [PhoneTypeId] [int] NOT NULL ,

  [PhoneNumber] [varchar] (30) not null

  ) ON [PRIMARY]

  GO

  -- Phone TypeID

  -- 1 = Home

  -- 2 = Cell

  -- 3 = work

  Insert into MemberPhone2

  (MemberID, PhoneTypeID, PhoneNumber)

  select 1, 1, '111-222-3333' union all

  select 1, 2, '222-222-3333' union all

  select 2, 1, '333-222-3333' union all

  select 3, 1, '444-555-3333' union all

  select 3, 2, '444-666-3333' union all

  select 3, 3, '444-777-3333'

  Desired Results :

  MemberID HomePhone CellPhone WorkPhone

  ------------------------------------------------------------

  1111-222-3333222-222-3333NULL

  2333-222-3333NULL NULL

  3 444-555-3333444-666-3333444-777-3333

  Any thoughts ??

  Thanks all ....

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  August 8, 2011 at 8:26 am

  #1365249

  Have you tried something like this?

  SELECT MemberID,

  MAX(CASE WHEN PhoneTypeID=1 THEN PhoneNumber END) AS HomePhone,

  MAX(CASE WHEN PhoneTypeID=2 THEN PhoneNumber END) AS CellPhone,

  MAX(CASE WHEN PhoneTypeID=3 THEN PhoneNumber END) AS WorkPhone

  FROM MemberPhone2

  GROUP BY MemberID

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Todd Young-359443

  Old Hand

  Points: 345

  More actions

  August 8, 2011 at 9:10 am

  #1365283

  No I didn't think of that - I gave it a shot and it works like a charm. Thank

  you so much for the help !!