Hello,

I would like to do a little query in T-SQL.

I have two tables:

PERSON (PERSONID, PERSON)
PHONE ( PHONEID, PERSONID, TYPE, PHONE) -- TYPE can be (business, fax,
business 2)

If I do a simple select I will get

SELECT PERSONID, PESON, TYPE, PHONE FROM PERSON PE INNER JOIN PHONE P
ON P.PERSONID = PE.PERSONID

Result:

PERSONID PERSON TYPE PHONE
1 Rose business xxxxxxxxx
1 Rose business xxxxxxxxxx
1 Rose business2 xxxxxxxxxxx
1 Rose fax
xxxxxxxxxxx


How to obtain the following

PERSONID PERSON BUSINESS BUSINESS2 BUSINESS3 FAX
1 Rose business xxxxxxxxx
xxxxxxx xxxxxxxxxx


any help appreciate
Thank you

Ina

It is better if the table can have the different type phones as columns
rather than rows.

To get the results with your current tables you need to do pivoting.

-- SQL Server 2000
SELECT P.personid, P.person,
MAX(CASE WHEN T.type = 'business' THEN T.phone END) AS business,
MAX(CASE WHEN T.type = 'business2' THEN T.phone END) AS business2,
MAX(CASE WHEN T.type = 'fax' THEN T.phone END) AS fax
FROM Person AS P
JOIN Phone AS T
ON P.personid = T.personid
GROUP BY P.personid, P.person

-- SQL Server 2005
SELECT personid, person,
[business],
[business2],
[fax]
FROM
(SELECT P.personid, P.person,
T.type, T.phone
FROM Person AS P
JOIN Phone AS T
ON P.personid = T.personid) AS T
PIVOT
(MAX(phone) FOR [type] IN ([business], [business2], [fax])) AS P

The above queries will work if you have unique phone type list per person.
If you may have duplicate types, then you have to add one more step to add a
sequence to the duplicate types:

-- SQL Server 2005
SELECT T.personid, T.person,
MAX(CASE WHEN T.type = 'business' AND seq = 1 THEN T.phone END) AS
business1,
MAX(CASE WHEN T.type = 'business' AND seq = 2 THEN T.phone END) AS
business2,
MAX(CASE WHEN T.type = 'fax' THEN T.phone END) AS fax
FROM
(SELECT P.personid, P.person,
T.type, T.phone,
ROW_NUMBER() OVER(PARTITION BY
P.personid, T.type
ORDER BY T.phone) AS seq
FROM Person AS P
JOIN Phone AS T
ON P.personid = T.personid) AS T
GROUP BY T.personid, T.person;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Feb 25, 2:50*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Oh thank you so much


Ina