Make rows to columns in SQL Server for each record

Question

I have a large(ish) DB. Simply put customer records. Now I have two tables; one is CustomerInfo and PhoneNumbers. Some sample data would be,

CustomerInfo
`````````````
CustID  |   CustName    |   CustomerLocation
--------+---------------+--------------------
1       |   Paul        |   Bristol
2       |   Eugin       |   Bournemouth
3       |   Francis     |   London

PhoneNumbers
````````````
PhoneID |   CustID  |   PhoneNumber
--------+-----------+----------------
1       |   1       |   0117123456
2       |   2       |   0120212345
3       |   2       |   0784256864
4       |   3       |   0204587895

Now as you could see, Paul and Francis have just one number, but Eugin has two. In a normal world, if I join the two tables as,

SELECT
    c.CustName,
    p.PhoneNumber
FROM
    CustomerInfo c
    JOIN 
    PhoneNumbers p
    ON c.CustID = p.CustID

I would get,

CustName    |   PhoneNumber
------------+--------------------
Paul        |   0117123456
Eugin       |   0120212345
Eugin       |   0784256864
Francis     |   0204587895

This is right, but I am running another Query that need the result to be,

CustName    |   PhoneNumber1    |   PhoneNumber2
------------+-------------------+---------------
Paul        |   0117123456      |   NULL
Eugin       |   0120212345      |   0784256864
Francis     |   0204587895      |   NULL

I could write a table variable from a function. But as this is going to be part of a Query, I was hoping if there were any other solution.

EDIT - I would like to highlight the section, as this is going to be part of a Query, I was hoping if there were any other solution, The actual query is going to be,

SELECT 
    per.[PersonId],
    per.[ClientReference],
    sal.SalutationName,
    per.[FirstName],
    per.[LastName],
    per.[DateOfBirth],
    per.[Password]
FROM 
    [Customers].[people].[Person] per
    JOIN 
    [Customers].[people].[Salutation] sal
    ON sal.SalutationId = per.SalutationId

What I would like is,

SELECT 
    per.[PersonId],
    per.[ClientReference],
    sal.SalutationName,
    per.[FirstName],
    per.[LastName],
    per.[DateOfBirth],
    per.[Password],
    pn.[PhoneNumber1], --Made up column, there is only one column in the pn table
    pn.[PhoneNumber2]  --Made up column, there is only one column in the pn table
FROM 
    [Customers].[people].[Person] per
    JOIN 
    [Customers].[people].[Salutation] sal
    ON sal.SalutationId = per.SalutationId
    JOIN 
    [Customers].[comms].[PhoneNumber] pn
    ON per.PersonId = pn.PersonId

Answer 1

You can use ROW_NUMBER() to give each phone number a rank within its customer ID, then use this to PIVOT the data:

SELECT  CustID,
        PhoneNumber1 = pvt.[1],
        PhoneNumber2 = pvt.[2],
        PhoneNumber3 = pvt.[3],
        PhoneNumber4 = pvt.[4]
FROM    (   SELECT  CustID, 
                    PhoneNumber,
                    RowNum = ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY Phonenumber)
            FROM    PhoneNumbers 
        ) AS pn
        PIVOT 
        (   MAX(Phonenumber)
            FOR RowNum IN ([1], [2], [3], [4]) -- INCREASE/DECREASE COLUMNS AS REQUIRED
        ) AS pvt;

If you have an unknown number of phone numbers and want to include them all where applicable, I would be inclined to use a single column and display a comma delimited list, which you can do using SQL Server's XML extensions. This is easier than using dynamic SQL, and also easier for anything dealing with the results as you have a known number of columns being returned:

SELECT  c.CustID,
        c.CustName,
        c.CustomerLocation,
        PhoneNumbers = STUFF((  SELECT  ',' + p.PhoneNumber
                                FROM    PhoneNumbers AS p
                                WHERE   p.CustID = c.CustID
                                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM    CustomerInfo  AS c;

Answer 2

This is what you want, Pivot with dynamic SQL

declare @maxcount as int = (select top 1 count(phonenumber) from PhoneNumbers group by custid order by count(phonenumber) desc)
declare @dynamicsql as nvarchar(max) = 'select * from
(
SELECT
    c.CustName,
    p.PhoneNumber,
    ''PhoneNumber''+convert(nvarchar(10),(row_number() over (partition by custname order by c.CustName))) as rn
FROM
    CustomerInfo c
    inner join 
    PhoneNumbers p
    ON c.CustID = p.CustID
) as sourcetable
pivot
(
    MAX(Phonenumber)
    FOR rn IN ('

declare @counter as int = 1
while (@counter<=@maxcount)
begin
set @dynamicsql = @dynamicsql +'[PhoneNumber'+convert(nvarchar(10),@counter)+'],'
set @counter=@counter+1
end

set @dynamicsql = substring(@dynamicsql,0,len(@dynamicsql))

set @dynamicsql = @dynamicsql + ')
)
as pivottable'

print @dynamicsql
exec (@dynamicsql)

For your reference

Answer 3

Thanks to Gareth, I managed to find my solution to this, I have accepted his answer, but thought I would share how I managed to get the desired result. This is the SQL I have used.

SELECT 
    per.[PersonId],
    per.[ClientReference],
    sal.SalutationName,
    per.[FirstName],
    per.[LastName],
    per.[DateOfBirth],
    per.[Password],
    tmpQ.PhoneNumber1,
    tmpQ.PhoneNumber2
FROM 
    [Customers].[people].[Person] per
    JOIN 
    [Customers].[people].[Salutation] sal
    ON sal.SalutationId = per.SalutationId
    JOIN 
    (SELECT  
         [PersonId],
         PhoneNumber1 = pvt.[1],
         PhoneNumber2 = pvt.[2]
     FROM (SELECT  
              [PersonId], 
              Number,
              RowNum = ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY Number)
           FROM [Customers].[comms].[PhoneNumber]) AS pn
           PIVOT (MAX(Number) FOR RowNum IN ([1], [2])) AS pvt
     ) tmpQ 
    ON tmpQ.PersonId = per.PersonId