SQL Pivot Table producing duplicates

Developers,

I am new to pivot tables, and am having a little problem with duplicates. My table, before pivoting looks like so:

location | food

Tennessee | pear

Tennessee | orange

Florida | orange

Florida | apple

Virginia | pear

Here is the code to pivot, which works fine:

SELECT PivotTable.location, [apple], [orange], [pear]
FROM
(SELECT location, food FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable

This produces an output like so:

Location | Apple | Orange | Pear

Tennessee | 0 | 1 | 1

Florida | 1 | 1 | 0

Virginia | 0 | 0 | 1

Which as I said works fine. However, I added new columns for comments to my original table, like so:

location | food | apple_comments | orange_comments | pear_comments

Tennessee | pear | NULL | NULL | NULL

Tennessee | orange | NULL | very juicy | NULL

Florida | orange | NULL | NULL | NULL

Florida | apple | crisp | NULL | NULL

Virginia | pear | NULL | NULL| tasty

Here is my altered pivot table to account for the comments:

SELECT PivotTable.location, [apple], [apple_comments], [orange], [orange_comments], [pear], [pear_comments]
FROM
(SELECT location, food, apple_comments, orange_comm开发者_StackOverflowents, pear_comments FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable

This produces an output like so:

Location | Apple | apple_comments | Orange | Orange_comments | Pear | Pear_comments

Tennessee | 0 | NULL | 0 | NULL | 1 | NULL

Tennessee | 0 | NULL | 1 | very juicy | 0 | NULL

Florida | 0 | NULL | 1 | NULL | 0 | NULL

Florida | 1 | crisp | 1 | NULL | 0 | NULL

Virginia | 0 | NULL | 1 | NULL | 1 | tasty

So, essentially, it is creating a duplicate row when comments are added for each entry where there are multiple locations. In the case of Virginia, there is only one entry, so the row turns out fine.

It almost seems like I need to do another pivot or something. Can anyone offer advice on where I'm going wrong?

Sorry. The desired output should look like so:

Location | Apple | apple_comments | Orange | Orange_comments | Pear | Pear_comments

Tennessee | 0 | NULL | 1 | very juicy | 1 | NULL

Florida | 1 | crisp | 1 | NULL | 0 | NULL

Virginia | 0 | NULL | 1 | NULL | 1 | tasty

Essentially, merging the duplicates into one row.

Thanks.

The fundamental problem is that you have effectively told the compiler to group by the comment column in addition to the food column. There are some solutions such as rolling up the comments into a delimited list like so:

Select location
    , Sum( Case When S.food = 'Apple' Then 1 Else 0 End ) As Apple
    , Stuff(
        (
        Select ', ' + S1.Apple_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Apple_Comments Is Not Null
        Group By S1.Apple_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Apple_Comments
    , Sum( Case When S.food = 'Orange' Then 1 Else 0 End ) As Orange
    , Stuff(
        (
        Select ', ' + S1.Orange_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Orange_Comments Is Not Null
        Group By S1.Orange_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Orange_Comments
    , Sum( Case When S.food = 'Pear' Then 1 Else 0 End ) As Pear
    , Stuff(
        (
        Select ', ' + S1.Pear_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Pear_Comments Is Not Null
        Group By S1.Pear_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Pear_Comments
From SomeTable As S
Group By S.location

Found the answer (utilizes the 'with CTE' and MAX functions):

;With CTE as (
SELECT PivotTable.location, [apple], [apple_comments], [orange], [orange_comments], [pear], [pear_comments]
FROM
(SELECT location, food, apple_comments, orange_comments, pear_comments FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable)
select location, MAX([apple]) as [apple], MAX([apple_comments]) as [apple_comments],MAX([orange]) as [orange], 
MAX([orange_comments]) as [orange_comments], MAX([pear]) as [pear], MAX([pear_comments]) as [pear_comments]
from CTE group by location