Using Pivot in SQL

  • Mysterio

    Old Hand

    Points: 360

    I am trying to create a query from this table: above is my table, and below is what I need my query to show.

    I tried many things: first, I tried using UNIONs, but it did not allow me to sum/average. Then I tried to experiment with the PIVOT function, but again, the issue is the percent requirement. One thing that I did not want to do is to create a table and then storing the information there. I think that this can be resolved by a query, so that is not a desirable solution. QueryTable

    % of total is calculated by getting total orders for the Product Category. Which in the example is 001, and then dividing total order for the subcate region over the total. Many, many thanks in advance!

     

  • pietlinden

    SSC Guru

    Points: 62895

    use a stored procedure, and create a variable in it, say @Total and ...

    SELECT @AllSalesAmount = 1.0 * SUM(Sales[Amount]);

    Then divide your sum by that in your pivot.

  • Ken McKelvey

    SSCoach

    Points: 18327

    It looks like you actually need to unpivot. This can be done by joining to some numbers, to increase the rows, and then use a CASE.

    If you can be bothered to post consumable test data someone might even give you an example.

  • Mysterio

    Old Hand

    Points: 360

    Use of SP is not allowed since we are analysts and without exec permission.

    Sorry didn't know that would be useful. Here.


    CREATE TABLE [dbo].[ProductOrders](
    [ProductCategory] [varchar](5) NULL,
    [ProductSubCategory] [varchar](5) NULL,
    [Total Orders Placed in North America] [int] NULL,
    [Total Orders Placed in South America] [int] NULL,
    [Total Orders Placed in North Africa] [int] NULL,
    [Total Orders Placed in South Africa] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'A', 2, 5, 12, 20)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'B', 5, 5, 14, 21)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'C', 1, 7, 15, 17)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'A', 6, 8, 11, 16)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'B', 3, 9, 13, 19)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'C', 2, 6, 14, 0)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'D', 8, 8, 13, 21)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'003', N'A', 2, 9, 10, 18)
    GO
    INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'003', N'B', 9, 0, 0, 29)
    GO
  • Ken McKelvey

    SSCoach

    Points: 18327

    Making the test rig more user friendly:

    SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
    GO
    CREATE TABLE #PO
    (
    ProductCategory varchar(5) NOT NULL
    ,ProductSubCategory varchar(5) NOT NULL
    ,[Total Orders Placed in North America] [int] NULL
    ,[Total Orders Placed in South America] [int] NULL
    ,[Total Orders Placed in North Africa] [int] NULL
    ,[Total Orders Placed in South Africa] [int] NULL
    ,PRIMARY KEY (ProductCategory, ProductSubCategory)
    );
    GO
    INSERT INTO #PO
    VALUES ('001', 'A', 2, 5, 12, 20)
    ,('001', 'B', 5, 5, 14, 21)
    ,('001', 'C', 1, 7, 15, 17)
    ,('002', 'A', 6, 8, 11, 16)
    ,('002', 'B', 3, 9, 13, 19)
    ,('002', 'C', 2, 6, 14, 0)
    ,('002', 'D', 8, 8, 13, 21)
    ,('003', 'A', 2, 9, 10, 18)
    ,('003', 'B', 9, 0, 0, 29);
    GO

    This should get you started:

    WITH NormalizedPO
    AS
    (
    SELECT PO.ProductCategory, PO.ProductSubCategory
    ,CASE N.N
    WHEN 1 THEN 'North America'
    WHEN 2 THEN 'South America'
    WHEN 3 THEN 'North Africa'
    WHEN 4 THEN 'North Africa'
    END AS Region
    ,CASE N.N
    WHEN 1 THEN [Total Orders Placed in North America]
    WHEN 2 THEN [Total Orders Placed in South America]
    WHEN 3 THEN [Total Orders Placed in North Africa]
    WHEN 4 THEN [Total Orders Placed in South Africa]
    END AS OrdersPlaced
    FROM #PO PO
    CROSS JOIN (SELECT V.N FROM (VALUES (1),(2),(3),(4)) V(N)) N
    )
    SELECT ProductCategory, ProductSubCategory, Region, OrdersPlaced
    -- Round this as you want. It is unlikely to add up to 100 without weighting.
    ,OrdersPlaced * 100.0 / SUM(OrdersPlaced) OVER (PARTITION BY ProductCategory) AS PercentPlaced
    FROM NormalizedPO;
  • Steve Collins

    Ten Centuries

    Points: 1090

    When CROSS JOIN with the tally table the resulting ordering may not align with Region.  It's not known which region will be 1 or 2 or ... unless joined back to something.  UNION ALL works.

    ;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
    select ProductCategory, ProductSubCategory, 'North America', [Total Orders Placed in North America] from #PO union all
    select ProductCategory, ProductSubCategory, 'South America', [Total Orders Placed in South America] from #PO union all
    select ProductCategory, ProductSubCategory, 'North Africa', [Total Orders Placed in North Africa] from #PO union all
    select ProductCategory, ProductSubCategory, 'South Africa', [Total Orders Placed in South Africa] from #PO)
    select
    ProductCategory, ProductSubCategory, Region, OrdersPlaced,
    (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Ken McKelvey

    SSCoach

    Points: 18327

    The tally table works, as long as the use of the numbers is consistent, and scans the table once.

    UNION ALL scans the table four times.

  • Steve Collins

    Ten Centuries

    Points: 1090

    SSC

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins

    Ten Centuries

    Points: 1090

    The bottom grouping is the code with tally ordered by 1,2,3.  The top is UNION ALL with same ordering

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins

    Ten Centuries

    Points: 1090

    Wait a sec.  North Africa is in the code twice!

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins

    Ten Centuries

    Points: 1090

    Ok ok I changed the decode to 'South Africa' and now it does work.  Misdiagnosed due to misleading code?  Yea, if it scans once and there's no dependency then it should be consistently applied?

    WITH NormalizedPO
    AS
    (
    SELECT PO.ProductCategory, PO.ProductSubCategory
    ,CASE N.N
    WHEN 1 THEN 'North America'
    WHEN 2 THEN 'South America'
    WHEN 3 THEN 'North Africa'
    WHEN 4 THEN 'South Africa'
    END AS Region
    ,CASE N.N
    WHEN 1 THEN [Total Orders Placed in North America]
    WHEN 2 THEN [Total Orders Placed in South America]
    WHEN 3 THEN [Total Orders Placed in North Africa]
    WHEN 4 THEN [Total Orders Placed in South Africa]
    END AS OrdersPlaced
    FROM #PO PO
    CROSS JOIN (SELECT V.N FROM (VALUES (1),(2),(3),(4)) V(N)) N
    )
    SELECT ProductCategory, ProductSubCategory, Region, OrdersPlaced
    -- Round this as you want. It is unlikely to add up to 100 without weighting.
    ,OrdersPlaced * 100.0 / SUM(OrdersPlaced) OVER (PARTITION BY ProductCategory) AS PercentPlaced
    FROM NormalizedPO
    order by 1,2,3;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • sgmunson

    SSC Guru

    Points: 110551

    Why not just UNPIVOT, as shown here in a CTE:

    CREATE TABLE dbo.ProductOrders (
    ProductCategory varchar(5) NULL,
    ProductSubCategory varchar(5) NULL,
    [Total Orders Placed in North America] int NULL,
    [Total Orders Placed in South America] int NULL,
    [Total Orders Placed in North Africa] int NULL,
    [Total Orders Placed in South Africa] int NULL
    );
    INSERT INTO dbo.ProductOrders (ProductCategory, ProductSubCategory, [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa])
    VALUES ('001', 'A', 2, 5, 12, 20),
    ('001', 'B', 5, 5, 14, 21),
    ('001', 'C', 1, 7, 15, 17),
    ('002', 'A', 6, 8, 11, 16),
    ('002', 'B', 3, 9, 13, 19),
    ('002', 'C', 2, 6, 14, 0),
    ('002', 'D', 8, 8, 13, 21),
    ('003', 'A', 2, 9, 10, 18),
    ('003', 'B', 9, 0, 0, 29);

    SET STATISTICS IO ON;

    WITH UNPIVOTED AS (

    SELECT
    REPLACE(RegionName, 'Total Orders Placed in ', '') AS Region,
    ProductCategory,
    ProductSubCategory,
    Orders
    FROM dbo.ProductOrders AS PO
    UNPIVOT (Orders FOR RegionName IN ([Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa])) AS UPVT
    )
    SELECT
    ProductCategory,
    ProductSubCategory,
    Region,
    Orders,
    Orders * 1.0 / SUM(Orders) OVER (PARTITION BY ProductCategory) AS PercentageOfOrders
    FROM UNPIVOTED
    ORDER BY
    ProductCategory,
    Region,
    ProductSubCategory;

    SET STATISTICS IO OFF;

    DROP TABLE dbo.ProductOrders;

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Steve Collins

    Ten Centuries

    Points: 1090

    sgmunson wrote:

    Why not just UNPIVOT, as shown here in a CTE:

    Both work.  The tally based is probably more efficient depending on scale.  It didn't occur to use a tally table the way Ken did.  I'm really liking that solution now.  Without the CASE WHEN's and just using iif's

    ;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
    select po.ProductCategory, po.ProductSubCategory,
    iif(n.n=1, 'North America',
    iif(n.n=2, 'South America',
    iif(n.n=3, 'North Africa',
    iif(n.n=4, 'South Africa', null)))),
    iif(n.n=1, [Total Orders Placed in North America],
    iif(n.n=2, [Total Orders Placed in South America],
    iif(n.n=3, [Total Orders Placed in North Africa],
    iif(n.n=4, [Total Orders Placed in South Africa], 0))))
    from #po po cross join dbo.fnTally(1, 4) n)
    select
    ProductCategory, ProductSubCategory, Region, OrdersPlaced,
    (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced
    from unpvt_cte;

     

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • sgmunson

    SSC Guru

    Points: 110551

    Steve Collins wrote:

    sgmunson wrote:

    Why not just UNPIVOT, as shown here in a CTE:

    Both work.  The tally based is probably more efficient depending on scale.  It didn't occur to use a tally table the way Ken did.  I'm really liking that solution now.  Without the CASE WHEN's and just using iif's

    ;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
    select po.ProductCategory, po.ProductSubCategory,
    iif(n.n=1, 'North America',
    iif(n.n=2, 'South America',
    iif(n.n=3, 'North Africa',
    iif(n.n=4, 'South Africa', null)))),
    iif(n.n=1, [Total Orders Placed in North America],
    iif(n.n=2, [Total Orders Placed in South America],
    iif(n.n=3, [Total Orders Placed in North Africa],
    iif(n.n=4, [Total Orders Placed in South Africa], 0))))
    from #po po cross join dbo.fnTally(1, 4) n)
    select
    ProductCategory, ProductSubCategory, Region, OrdersPlaced,
    (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced
    from unpvt_cte;

     

    True, but I'm somewhat partial to PIVOT and UNPIVOT as they are fairly easy to code, and it's not that difficult to make them dynamic with dynamic SQL.   Part of that partiality is due to having read up on those "then new" features of SQL  2005 just the night before I was to start work on my very 1st IT contract, in a book on SQL 2005 that I bought for that purpose, not having had any prior exposure to that version, and then having a working query that used both of them on my first day on the job.

    • This reply was modified 1 month, 2 weeks ago by  sgmunson. Reason: Oops... hit button by accident before I was done typing

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • ScottPletcher

    SSC Guru

    Points: 98559

    Gotta admit, I'm not a fan of PIVOT nor UNPIVOT.  I just don't care for the syntax / approach.  It seems counter-intuitive to me, naturally YMMV.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply