cross tab display

Question

cross tab display

Murphy'sLaw

Right there with Babe

Points: 725
More actions
February 2, 2012 at 1:26 pm

#254741

Create Table PivotTable (ID int, Name varchar(12), type varchar(18), PhoneNumber varchar(6))
insert into PivotTable (ID,Name,type,PhoneNumber)
select 1,'tom','Home',1
union all
select 1,'tom','Office',2
union all
select 2,'trinity','Home',3
union all
select 2,'trinity','Office',4
I am tryin to get phone number in cross tab pivot result like this
id Name home Office
1 tom 1 2
2 trinity 3 4
any help?
thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

what have you tried so far?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Murphy'sLaw Right there with Babe Points: 725 More actions · Answer 2

J Livingston SQL (2/2/2012)
what have you tried so far?

This is just a sample snippet of the code. I have a much larger table to take care of. I was some logical inputs because i need to get this done asap.

David Wiseman SSCommitted Points: 1713 More actions · Answer 3

How about this:

SELECT ID,

NAME,

MAX(CASE WHEN type='Home' THEN PhoneNumber ELSE NULL END) AS [home],

MAX(CASE WHEN type='Office' THEN PhoneNumber ELSE NULL END) AS [Office]

FROM PivotTable

GROUP BY ID,Name

Hope this helps,

David

DBA Dash - Free, open source monitoring for SQL Server

David Wiseman SSCommitted Points: 1713 More actions · Answer 4

I've never cared much for the new syntax, but you can also use:

SELECT ID,

NAME,

pvt.Home,

pvt.Office

FROM PivotTable

PIVOT(

MAX(PhoneNumber)

FOR [TYPE] IN([Home],[Office])

) AS pvt

DBA Dash - Free, open source monitoring for SQL Server

Murphy'sLaw Right there with Babe Points: 725 More actions · Answer 5

wiseman82 (2/2/2012)
I've never cared much for the new syntax, but you can also use:
SELECT ID,
NAME,
pvt.Home,
pvt.Office
FROM PivotTable
PIVOT(
MAX(PhoneNumber)
FOR [TYPE] IN([Home],[Office])
) AS pvt

thanks.really helpful.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

wiseman82 (2/2/2012)
I've never cared much for the new syntax, but you can also use:

Ditto that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)