SQL PIVOT

[brandonb]

I have the data:

(this forum blows chunks as it seems to trim all whitespace)

id..............account_num........firstname.....lastname....phone type.......phone number
132...........8547809..............MICHAEL......CLARK........1.....................5555555555
132...........8547809..............MICHAEL......CLARK .......4.....................9999999999

I'm trying to create 1 row for that with the columns

id..............account_num........firstname........lastname.....homephone.......worknumber
132............8547809..............MICHAEL.........CLARK........5555555555......9999999999

But I can't seem to get the PIVOT command to work and do that. Is it just not possible with PIVOT? The phone type is basically the pivot field. Depending on the phone type depends if its home/work, etc.

Can anybody lead me down the path to pivot this correctly?

 

[KLin]

the Pivot statement is used for data summation, not the way you want to use it.

http://www.eggheadcafe.com/sof...-pivot-query-help.aspx

SELECT Acc.ID, Acc.AccountNum, Acc.FirstName, Acc.LastName, HomePhone, WorkPhone FROM
(
(SELECT DISTINCT ID, AccountNum, FirstName, LastName from table1) Acc LEFT JOIN
(SELECT ID, PhoneNumber As HomePhone from table1 Where PhoneType = 1) Home on Acc.ID = Home.ID LEFT JOIN
(SELECT ID, PhoneNumber As WorkPhone from table1 Where PhoneType = 4) Work on Acc.ID = Work.ID
)

Just add more subqueries for each phonetype.

 

[KLin]

DECLARE @Table TABLE(ID int, Acc int, FirstName varchar(50), LastName varchar(50), PhoneType bigint, PhoneNumber bigint)

INSERT INTO @Table(ID, Acc, FirstName, LastName, PhoneType, PhoneNumber) VALUES(132,8547809,'MICHAEL','CLARK',1,5555555555)
INSERT INTO @Table(ID, Acc, FirstName, LastName, PhoneType, PhoneNumber) VALUES(132,8547809,'MICHAEL','CLARK',4,9999999999)

SELECT * from @Table

SELECT Acc.ID, Acc.Acc, Acc.FirstName, Acc.LastName, HomePhone, WorkPhone FROM
(
(SELECT DISTINCT ID, Acc, FirstName, LastName from @Table) Acc LEFT JOIN
(SELECT ID, PhoneNumber As HomePhone from @Table Where PhoneType = 1) Home on Acc.ID = Home.ID LEFT JOIN
(SELECT ID, PhoneNumber As WorkPhone from @Table Where PhoneType = 4) Work on Acc.ID = Work.ID
)

Used that statement as a test, and it worked.

 

[brandonb]

Thank you much! I will avoid the PIVOT and do it the old fashioned way. =)

 

[jmakcen]

This may be a day too late...

I've recently been trying to learn the SQL PIVOT function as well. Yeah, it seems a lot more complicated than it should be - however I've found that you can use the MIN() function as the summation function to return the desired data.

Try this:

SELECT id, account_num, firstname, lastname, [1] as homephone, [4] as workphone
FROM (
SELECT id, account_num, firstname, lastname, phonetype, phonenumber FROM table ) as sourcetable
PIVOT (
min (phonenumber)
FOR phonetype IN (
[1], [4] )
) as pivottable

Obviously, in my example, you'd have to hardcode every phone type.

 

[KLin]

Originally posted by: jmakcen
This may be a day too late...

I've recently been trying to learn the SQL PIVOT function as well. Yeah, it seems a lot more complicated than it should be - however I've found that you can use the MIN() function as the summation function to return the desired data.

Try this:

SELECT id, account_num, firstname, lastname, [1] as homephone, [4] as workphone
FROM (
SELECT id, account_num, firstname, lastname, phonetype, phonenumber FROM table ) as sourcetable
PIVOT (
min (phonenumber)
FOR phonetype IN (
[1], [4] )
) as pivottable

Obviously, in my example, you'd have to hardcode every phone type.

Well that works too. Sweet :thumbsup:


PS. See you in a couple more years for your 4th post .

 

[jmakcen]

Ha, lol yeah - long time lurker. I never really have much of an urge to post, however having just been through this I was inspired.

 

[BoberFett]

What I want to know is who designed your database and needs to work on their normalization?

But subqueries would be another approach.

select distinct id, account_num, firstname, lastname,
(select phone_number from table where id = t.id and phone_type=1) as home_number,
(select phone_number from table where id = t.id and phone_type=4) as work_number
from table t

 

[KLin]

Originally posted by: BoberFett
What I want to know is who designed your database and needs to work on their normalization?

But subqueries would be another approach.

select distinct id, account_num, firstname, lastname,
(select phone_number from table where id = t.id and phone_type=1) as home_number,
(select phone_number from table where id = t.id and phone_type=4) as work_number
from table t

This also works. :thumbsup: