Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Swapping rows and columns help - PIVOT and UNPIVOT?

    I have dataset in which i need to swap some rows and columns. I can do this with a ton of self joins and unions but I was thinking there was a better way using pivot/unpivot but I just can get it working right. Here is some sample data:

    Code:
    create table #test(
    Prog varchar(50)
    ,FiscalYr int
    ,FiscalMth int
    ,BuyerType varchar(50)
    ,PreMatchDmd int
    ,CatMatchDmd int
    ,PostMatchDmd int)
    
    INSERT INTO #test values
    ('Email-Marketing',2015,5,'Existing',205578,168278,37299)
    ,('Email-Marketing',2015,5,'New',62902,39668,23233)
    ,('Email-Trigger',2015,5,'New',119779,0,119779)
    ,('Email-Trigger',2015,5,'Existing',88370,0,88370)
    What I am trying to do is move the columns PreMatchDmd, CatMatchDmd, and PostMatchDmd to rows of a new column called GroupNm, and the values for BuyerType ("existing" or "new") to be new column headers. This is what I am trying to get the final results to look like:
    Click image for larger version. 

Name:	ORlLUek.png 
Views:	2 
Size:	12.9 KB 
ID:	16322

    Here is the method I am using that is pretty ugly, using joins and unions. I am just wondering if there is a cleaner, more streamlined way.
    Code:
    SELECT a.*,b.NewBuyerDemand
    FROM 
    (SELECT prog,fiscalyr,fiscalmth,'Pre-Matchback Demand' AS GroupName,prematchdmd AS ExistingBuyerDemand
    FROM #test
    WHERE BuyerType = 'Existing Buyer Demand') a
    INNER JOIN
    (SELECT prog,fiscalyr,fiscalmth,'Pre-Matchback Demand' AS GroupName,prematchdmd AS NewBuyerDemand
    FROM #test
    WHERE BuyerType = 'New Buyer Demand') b
    ON a.prog = b.prog AND a.fiscalyr = b.fiscalyr AND a.fiscalmth = b.fiscalmth
    
    UNION ALL
    
    SELECT a.*,b.NewBuyerDemand
    FROM 
    (SELECT prog,fiscalyr,fiscalmth,'Cat-Matchback Demand' AS GroupName,CatMatchDmd AS ExistingBuyerDemand
    FROM #test
    WHERE BuyerType = 'Existing Buyer Demand') a
    INNER JOIN
    (SELECT prog,fiscalyr,fiscalmth,'Cat-Matchback Demand' AS GroupName,CatMatchDmd AS NewBuyerDemand
    FROM #test
    WHERE BuyerType = 'New Buyer Demand') b
    ON a.prog = b.prog AND a.fiscalyr = b.fiscalyr AND a.fiscalmth = b.fiscalmth
    
    UNION ALL
    
    SELECT a.*,b.NewBuyerDemand
    FROM 
    (SELECT prog,fiscalyr,fiscalmth,'Post-Matchback Demand' AS GroupName,PostMatchDmd AS ExistingBuyerDemand
    FROM #test
    WHERE BuyerType = 'Existing Buyer Demand') a
    INNER JOIN
    (SELECT prog,fiscalyr,fiscalmth,'Post-Matchback Demand' AS GroupName,PostMatchDmd AS NewBuyerDemand
    FROM #test
    WHERE BuyerType = 'New Buyer Demand') b
    ON a.prog = b.prog AND a.fiscalyr = b.fiscalyr AND a.fiscalmth = b.fiscalmth
    Here is a pivot statement I was able to get semi-working but it puts the values for new and existing on separate lines with the other being null. This also breaks when I try to add the GroupNm column.
    Code:
    SELECT prog,fiscalyr,fiscalmth,[New Buyer Demand], [Existing Buyer Demand]
    FROM #test
    PIVOT (sum(prematchdmd) for buyertype in([New Buyer Demand], [Existing Buyer Demand])) AS pvt
    Any help is appreciated.
    Last edited by clawlan; 04-29-15 at 14:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •