Search code examples
sqlsql-serverpivot

SQL Pivot Two Groups

I have been through multiple pages of pivot examples, but I am still doing something wrong. Short explanation, I have three groups of tables, Goals, Groups, and Sales for example.

I have managed to get the three sources boiled down to this. I could manipulate this in another way if there is a better suggestion.

compiled data

Through my pivot logic I have built this, but it isn't right. It should be down to two records. One for Group 1, one for Group 2

my pivot results

What I am actually expecting is this

expected results

Here is how I am pivoting that compiled data in the first image

DECLARE @tbl TABLE (
    Id INT NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    GoalName VARCHAR(50) NOT NULL,
    GoalAmount INT NOT NULL,
    SalesName VARCHAR(50) NOT NULL,
    SalesAmount DECIMAL(19,2) NULL
)

INSERT INTO @tbl(Id, [Name], GoalName, GoalAmount, SalesName, SalesAmount)
VALUES (100000, 'Group 1', 'Target 1 Goal', 1000000, 'Target 1 Sales', 31380.00)
     , (100000, 'Group 1', 'Target 2 Goal', 500000, 'Target 2 Sales', 0.00)
     , (100000, 'Group 1', 'Target 3 Goal', 100000, 'Target 3 Sales', 8520.00)
     , (100529, 'Group 2', 'Target 1 Goal', 750000, 'Target 1 Sales', NULL)
     , (100529, 'Group 2', 'Target 2 Goal', 400000, 'Target 2 Sales', NULL)

SELECT *
FROM (
    SELECT gm.Id, gm.[Name], gm.GoalName, gm.GoalAmount, gm.SalesName, gm.SalesAmount
    FROM @tbl gm
) src
    PIVOT (
        MAX(GoalAmount)
        FOR GoalName IN ([Target 1 Goal], [Target 2 Goal], [Target 3 Goal])
    ) pivGoals
    PIVOT (
        MAX(SalesAmount)
        FOR SalesName IN ([Target 1 Sales], [Target 2 Sales], [Target 3 Sales])
    ) pivSales

I don't know what I am missing. I have tried grouping, but that doesn't change anything. This is maybe the third time I have tried using a pivot, so I am lost.

Here are a couple of the other articles that I have tried to read.

https://dba.stackexchange.com/questions/192524/how-to-pivot-on-multiple-columns-in-sql-server https://www.tangrainc.com/blog/2009/01/pivoting-on-multiple-columns/

Copyable Table Data

Id Name GoalName GoalAmount SalesName SalesAmount
100000 Group 1 Target 1 Goal 1000000 Target 1 Sales 31380.00
100000 Group 1 Target 2 Goal 500000 Target 2 Sales 0.00
100000 Group 1 Target 3 Goal 100000 Target 3 Sales 8520.00
100529 Group 2 Target 1 Goal 750000 Target 1 Sales NULL
100529 Group 2 Target 2 Goal 400000 Target 2 Sales NULL

Edited to provide data and table creation

Solution

  • You can do a GROUP BY on your data to get what you want:

    select id, name, /*sums added*/ sum([Target 1 Goal]), sum([Target 2 Goal]), sum([Target 3 Goal]), sum([Target 1 Sales]), sum([Target 2 Sales]), sum([Target 3 Sales])
from (
    select 100000 As id, 'Group 1' AS name, 'Target 1 goal' GoalName, 1000000 as goalamount, 'Target 1 sales' as SalesName, 31380 as salesamount
    union all
    select 100000 As id, 'Group 1' AS name, 'Target 2 goal' GoalName, 500000 as goalamount, 'Target 2 sales' as SalesName, 0 as salesamount
    union all
    select 100000 As id, 'Group 1' AS name, 'Target 3 goal' GoalName, 100000 as goalamount, 'Target 2 sales' as SalesName, 8520 as salesamount
    union all
    select 10529 As id, 'Group 2' AS name, 'Target 1 goal' GoalName, 750000 as goalamount, 'Target 1 sales' as SalesName, null as salesamount
    union all
    select 10529 As id, 'Group 2' AS name, 'Target 2 goal' GoalName, 400000 as goalamount, 'Target 2 sales' as SalesName, null as salesamount
    
    ) src
    PIVOT (
        MAX(GoalAmount)
        FOR GoalName IN ([Target 1 Goal], [Target 2 Goal], [Target 3 Goal])
    ) pivGoals
    PIVOT (
        MAX(SalesAmount)
        FOR SalesName IN ([Target 1 Sales], [Target 2 Sales], [Target 3 Sales])
    ) pivSales
group by id,name -- Added