SQL Pivot with String

Question

I have two tables in SQL Server: Customer and Address

Customer Table:

CustomerID  FirstName  LastName
----------- ---------- ----------
1           Andrew     Jackson         
2           George     Washington

Address Table:

AddressID   CustomerID  AddressType City
----------- ----------- ----------- ----------
1           1           Home        Waxhaw     
2           1           Office      Nashville    
3           2           Home        Philadelphia

This is the output that I need:

CustomerID  Firstname  HomeCity      OfficeCity
----------- ---------- ----------    ----------
1           Andrew     Waxhaw        Nashville
2           George     Philadelphia  Null

This is my query, but not getting the right result:

SELECT CustomerID, Firstname, HOme as HomeCity, Office as OfficeCity FROM 
   (SELECT C.CustomerID, C.FirstName, A.AddressID, A.AddressType, A.City 
    FROM Customer C, Address A 
    WHERE C.CustomerID = A.CustomerID)as P
PIVOT (MAX(city) FOR AddressType in ([Home],[Office])) as  PVT

This is the result that I am getting:

CustomerID  Firstname  HomeCity      OfficeCity
----------- ---------- ----------    ----------
1           Andrew     Waxhaw        NULL
1           Andrew     NULL          Nashville
2           George     Philadelphia  Null

As you can see Customer 1 is showing up twice in the final result. Is it possible to get only one row per customer?

I looked up this example, but didn't help:http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns

Thanks

score 26 · Accepted Answer · answered Apr 25 '12 at 16:22

26

It is giving this row because you have AddressID in the select list for you subquery "P". So even though you don't have AddressID in you top level select this, the PIVOT function is still grouping by it. You need to change this to:

SELECT  CustomerID, Firstname, Home as HomeCity, Office as OfficeCity 
FROM    (   SELECT C.CustomerID, C.FirstName, A.AddressType, A.City 
            FROM #Customer C, #Address A 
            WHERE C.CustomerID = A.CustomerID
        ) AS P
        PIVOT 
        (   MAX(city) 
            FOR AddressType in ([Home],[Office])
        ) AS  PVT

Although I would be inclined to use an explicit INNER JOIN rather than an implicit join between customer and Address.

answered Apr 25 '12 at 16:22

GarethD

68,045
10
83
123

Wonderful, that worked! Thanks!! I didn't realize addressID was causing it. I got used to implicit joins, but I will start using INNER JOINs as you have mentioned. – kthiagar Apr 25 '12 at 16:26
1

In this scenario it doesn't make a lot of difference whether implicit or inner joins are used, and there are a number of different discussions about the subject e.g [here](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). My personal view is that explicit joins are easier to read, allow for easier change between 'INNER` AND 'OUTER` joins, and also reduce the likely hood of accidental cross joins by missing a where clause. – GarethD Apr 25 '12 at 16:31

score 13 · Answer 2 · edited Apr 25 '12 at 16:27

13

I would write it like this instead:

SELECT C.CustomerID, C.Firstname,
    Home.City as HomeCity,
    Office.City as OfficeCity
FROM Customer C
    LEFT JOIN Address Home
        on Home.CustomerID = C.CustomerID and Home.AddressType = 'Home'
    LEFT JOIN Address Office
        on Office.CustomerID = C.CustomerID and Office.AddressType = 'Office'

edited Apr 25 '12 at 16:27

GarethD

68,045
10
83
123

answered Apr 25 '12 at 16:19

Chad

7,279
2
24
34

1

Thanks, this looks like another good way of doing this without using PIVOT. – kthiagar Apr 25 '12 at 16:29
This works well if you don't need to aggregate anything. I had a table with some string columns I unpivoted, normalized, and then used to this to rebuild – J Scott Oct 15 '21 at 21:34

SQL Pivot with String

2 Answers2

Linked