column value in a single row

问题:

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

回答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'
   

回答2:

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

回答3:

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]