none
Pivot Query in Groups

    Question

  • I need help to create a pivot query. I can write a query to create a dataset as shown in the above image and I need to pivot this query dataset into something like the middlw image. I attached the sql script to create the straight forward dataset.


    Kahlua

    Tuesday, March 21, 2017 5:12 AM

All replies

  • Please send the DDL to create all you tables and INSERT statements for sample data
    Tuesday, March 21, 2017 5:28 AM
  • Just guessing 

    SELECT storenum,

    SUM(CASE WHEN market=82 THEN total END ) totlasales,

    SUM(CASE WHEN market=82 AND month ='Jan 'THEN total END ) Jansales,

    SUM(CASE WHEN market=82 AND month ='Feb 'THEN total END ) fEBsales,

    ....................................

    FROM tbl GROUP BY storenum


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 21, 2017 6:56 AM
    Moderator
  • Thank you for the reply. To begin with, here are the DDL to create the tables. Basically, a Market (MarketCode) has many Stores (StoresNum), same Product has diff cost on diff Markets. an Order consists many OrderDetails and each OrderDetail links to the ProductID and MarketID to calculate the Total Cost (Quantity*Cost(Product table))
    CREATE TABLE	[dbo].[Market](
    				[MarketID] [int] NOT NULL,
    				[MarketCode] [int] NOT NULL)
    
    CREATE TABLE	[dbo].[Store](
    				[StoreID] [int] NOT NULL,
    				[MarketID] [int] NOT NULL,
    				[StoreNum] [int] NOT NULL)
    
    CREATE TABLE	[dbo].[Product](
    				[ProductID] [int] NOT NULL,
    				[MarketID] [int] NOT NULL,
    				[Product] varchar(75) NOT NULL,
    				[Cost]	money Not Null)
    
    CREATE TABLE	[dbo].[OrderDetail](
    				[OrderDetailID] [int] NOT NULL,
    				[OrderID] [int] NOT NULL,
    				[MarketID] [int] NOT NULL,
    				[ProductID] [int] NOT NULL,
    				[OrderQuantity] [int] NOT NULL)
    
    CREATE TABLE	[dbo].[Orders](
    				[OrderID] [int] NOT NULL,
    				[StoreID] [int] NOT NULL,
    				[OrderDate] [datetime] NULL)


    Kahlua


    • Edited by pmak Tuesday, March 21, 2017 2:42 PM
    Tuesday, March 21, 2017 2:01 PM
  • Sorry, I am providing a poor quality reply. Your script columns don't match the query. Part of the query is cut off. And I have no data in the tables for the test. I'm not sure this will even run, however this might help you get started:

    SELECT store_num AS Market,  
    [82], [121], [146], [193] 
    FROM 
    (SELECT
        E.MarketCode AS Market,
        B.StoreNum AS store_num,
        DATENAME(month, A.OrderDate) AS month,
        SUM(C.OrderQuantity * D.Cost) AS total
    FROM dbo.Orders AS A
    LEFT JOIN dbo.Store AS B ON A.StoreID = B.StoreID
    LEFT JOIN dbo.OrderDetail AS C ON A.OrderID = C.OrderID
    LEFT JOIN dbo.Product AS D ON C.MarketID = D.MarketID
    LEFT JOIN dbo.Market AS E ON  D.MarketID = E.MarketID
    GROUP BY E.MarketCode,
      B.StoreNum,
      DATENAME(month, A.OrderDate),
      DATEPART(month, A.OrderDate)
    ) AS SourceTable 
    PIVOT 

    AVG(total) 
    FOR Market IN ([82], [121], [146], [193]) 
    ) AS PivotTable; 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 22, 2017 3:54 PM
    Moderator