3

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
Improve this question
2
  • 1
    This is a pivot query. Important question: Can you have "unlimited" phone numbers per customer? Or is there a limit ? Commented Jun 12, 2015 at 11:36
  • @SQLPolice, I do not have at the present time more than 2, however. Even if we have, I would want just the first two. Thanks, I will look into this. :) Commented Jun 12, 2015 at 12:23

3 Answers 3

Reset to default
5

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@SQLPolice I would increase the number of columns, which is done by changing the number arguments in the PIVOT function right next to the comment that states "INCREASE/DECREASE COLUMNS AS REQUIRED"
Yes, but what if you want to be totally flexible ? ... write 356 columns ? .... The only solution which supports "unlimited" phone number columns is to write a procedure with dynamic SQL.
I agree to the XML solution, this is a very godo solution in many cases. It depends on how the solution will be needed. If you want to load the view into excel spreadsheet, then you would again need a solution with "real" columns.
There is no mention of this requirement in the question, so I don't like to jump to conclusions about what the OP may or may not require. Since dynamic SQL should be used sparingly, and with caution, I would not go out of my way to recommend it where it may not be required.
I agree to your opinion to dynamic SQL. That's why I asked the OP in the comment. Dynamic SQL can be great, but can also open up a pandora's box, so better weigh carefully.
|
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

enter image description here

Improve this answer

5 Comments

Pravin, there is no doubt this works, but as I mentioned I am using this as part of another query that has other JOINS, so in that case, how would I use a Dynamic SQL along with my existing Query?
Put that into string query
I have not taken this as my answer, but I really do like the way you have gone about doing this, I will play with this. Cheers.:)
Have fun. Cheers! @PaulFrancis
Hi @PaulFrancis, I have mailed you. :) see you there.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.