column value in a single row

Question

Primary table

Id name 

1 xxxxx
2 yyyyy
3 zzzzzz

Foreign table

Id   phonetype        phoneno 

1    home             111111
1    work             222222
1    mob              333333
2    work             444444
2    mob              555555

I need to display

Name    workphone   homephone        mob

xxxxx   222222       111111        333333
yyyyy   444444        null         555555

How to write the query in sql server 2008

Answer 1

Here are two more methods, just for completeness:

1. SELECT
     p.name,
     MAX(CASE t.phonetype WHEN 'work' THEN t.phoneno END) AS workphone,
     MAX(CASE t.phonetype WHEN 'home' THEN t.phoneno END) AS homephone,
     MAX(CASE t.phonetype WHEN 'mob' THEN t.phoneno END) AS mobilephone
   FROM people p
     INNER JOIN phones t ON p.Id = t.Id
   GROUP BY p.Id, p.Name
   

2. SELECT
     p.name,
     w.phoneno AS workphone,
     h.phoneno AS homephone,
     m.phoneno AS mobilephone
   FROM people p
     LEFT JOIN phones w ON p.Id = w.Id AND w.phonetype = 'work'
     LEFT JOIN phones h ON p.Id = h.Id AND h.phonetype = 'home'
     LEFT JOIN phones m ON p.Id = m.Id AND m.phonetype = 'mob'
   

Answer 2

In addition to Marc_s solution, you can also use PIVOTs to achieve this:

SELECT [Name], [home], [work], [mob]
FROM
(
    SELECT [Name], phonetype, phoneno
    FROM Customer c join ContactDetail cd on c.Id = cd.id
) t
PIVOT
(
    MIN(PhoneNo) FOR [phonetype] IN ([home], [work], [mob]) 
) AS pvt
ORDER BY pvt.[Name]

Answer 3

Try something like this:

SELECT 
    pt.NAME,
    (SELECT PhoneNo FROM dbo.Secondary s WHERE s.ID = pt.ID AND s.PhoneType = 'home') 'Home',
    (SELECT PhoneNo FROM dbo.Secondary s WHERE s.ID = pt.ID AND s.PhoneType = 'work') 'Work',
    (SELECT PhoneNo FROM dbo.Secondary s WHERE s.ID = pt.ID AND s.PhoneType = 'mob') 'Mobile'
FROM dbo.PrimaryTable pt