Pivot table or?

Transact-SQL
1

Hi,

I have a contact list for a person.
A person can have 2 contacts.
I would like to split the contacts like below.

MyTable
number bigint, (unique id to the person)
stdlastn varchar(100), (person last name)
stdfirstn varchar(100), (peson first name)
strLastName varchar(100), (contact last name)
strFirstName varchar(100), (contact first name)
strTelephoneNumber varchar(100),
intPersonRelationship int (1 = father 2 = mother)

15 Doe John Doe Amel (555) 555-1111 2
15 Doe John Doe Julie (555) 555-2111 1

So i would my final result to be

15 Doe John Doe Amel (555) 555-1111 Doe Julie (555) 555-2111

i try something like this work but only for one entry

SELECT DISTINCT
max(CASE WHEN 1 = intPersonRelationship THEN number END) as number,
max(CASE WHEN 1 = intPersonRelationship THEN stdlastn END) as stdlastn,
max(CASE WHEN 1 = intPersonRelationship THEN stdfirstn END) as stdfirstn,
max(CASE WHEN 1 = intPersonRelationship THEN strFirstName END) as strFirstName,
max(CASE WHEN 1 = intPersonRelationship THEN strLastName END) as strLastName,
max(CASE WHEN 2 <> intPersonRelationship THEN strTelephoneNumber END) as strTelephoneNumber2
max(CASE WHEN 2 <> intPersonRelationship THEN strFirstName END) as strFirstName2,
max(CASE WHEN 2 <> intPersonRelationship THEN strLastName END) as strLastName2,
max(CASE WHEN 1 = intPersonRelationship THEN strTelephoneNumber END) as strTelephoneNumber,

FROM #temp

Tks

2

Ok i think i got it
is there a better way?

SELECT DISTINCT
number,
max(CASE WHEN 1 = intPersonRelationship THEN stdlastn END) as stdlastn,
max(CASE WHEN 1 = intPersonRelationship THEN stdfirstn END) as stdfirstn,
max(CASE WHEN 1 = intPersonRelationship THEN strFirstName END) as strFirstName,
max(CASE WHEN 1 = intPersonRelationship THEN strLastName END) as strLastName,
max(CASE WHEN 2 <> intPersonRelationship THEN strTelephoneNumber END) as strTelephoneNumber2
max(CASE WHEN 2 <> intPersonRelationship THEN strFirstName END) as strFirstName2,
max(CASE WHEN 2 <> intPersonRelationship THEN strLastName END) as strLastName2,
max(CASE WHEN 1 = intPersonRelationship THEN strTelephoneNumber END) as strTelephoneNumber,

FROM #temp
group by number

3

select
father.number,
father.stdlastn as Student_Last_Name,
father.stdfirstn as Student_First_Name,
father.strLastName AS Father_Last_Name,
father.strFirstName AS Father_First_Name,
father.strTelephoneNumber AS Father_Telephone_Number,
mother.strLastName AS Mother_Last_Name,
mother.strFirstName AS Mother_First_Name,
mother.strTelephoneNumber AS Mother_Telephone_Number,
from #temp father
join #temp mother
on father.number - mother.number
and mother.intPersonRelationship = 2
where father.intPersonRelationship = 1

4

select
father.number,
father.stdlastn as Student_Last_Name,
father.stdfirstn as Student_First_Name,
father.strLastName AS Father_Last_Name,
father.strFirstName AS Father_First_Name,
father.strTelephoneNumber AS Father_Telephone_Number,
mother.strLastName AS Mother_Last_Name,
mother.strFirstName AS Mother_First_Name,
mother.strTelephoneNumber AS Mother_Telephone_Number,
from #temp father
join #temp mother
on father.number = mother.number _- In this reply I fixed a typo the equal sign was a dash.
and mother.intPersonRelationship = 2
where father.intPersonRelationship = 1

1 Like
5

Well many tks work perfect