CODEWITHSUNDEEP
sqlsql-serverpivot
Joe Resler
Joe Resler

Reputation: 101

How to pivot a column into a row for sql query

I have budget information that has come to me in the following format (excel) company, salesrep, week1, week2, week3 and each of the week columns represent a budget amount. The problem is that I am trying to join with a table that has the following columns weeknumber, company, salesrep, budget amount. So obviously I can't join by by doing weeknumber = week1. How can I structure the join to allow for this. I can go through the entire excel spreadsheet 100k rows and give each row a week number and add a column that shows the budget amount. But that would be quite a bit of work. Is there anyway to accomplish this programmatically? Your help is greatly appreciated. EDIT*** as mentioned by the comments. I have used the following query:

SELECT CompanyKey, Subcategory, ItemKey, CustomerKey, SalesRepKey, Pounds
FROM 
(SELECT CompanyKey, Company, CompanyDesc, Subcategory, SubcategoryDesc, 
ItemKey, ItemDesc, CustomerKey, CustomerDesc, SalesRepKey, SalesRep, Wk_1, 
Wk_2, Wk_3, Wk_4, Wk_5, Wk_6, Wk_7, Wk_8, Wk_9, Wk_10, Wk_11, Wk_12, Wk_13, 
Wk_14,
Wk_15, Wk_16, Wk_17, Wk_18, Wk_19, Wk_20, Wk_21, Wk_22, Wk_23, Wk_24, Wk_25, 
Wk_26, Wk_27, Wk_28, Wk_29, Wk_30, Wk_31, Wk_32, Wk_33, Wk_34, Wk_35, Wk_36, 
Wk_37, Wk_38, Wk_39, Wk_40, Wk_41, Wk_42, Wk_43, Wk_44, Wk_45, Wk_46, Wk_47,
Wk_48, Wk_49, Wk_50, Wk_51, Wk_52, Wk_53 FROM Budget_Master) p
UNPIVOT
(Pounds FOR [week] IN 
  (Wk_1, Wk_2, Wk_3, Wk_4, Wk_5, Wk_6, Wk_7, Wk_8, Wk_9, Wk_10, Wk_11, 
Wk_12, Wk_13, Wk_14,
Wk_15, Wk_16, Wk_17, Wk_18, Wk_19, Wk_20, Wk_21, Wk_22, Wk_23, Wk_24, Wk_25, 
Wk_26, Wk_27, Wk_28, Wk_29, Wk_30, Wk_31, Wk_32, Wk_33, Wk_34, Wk_35, Wk_36, 
Wk_37, Wk_38, Wk_39, Wk_40, Wk_41, Wk_42, Wk_43, Wk_44, Wk_45, Wk_46, Wk_47,
Wk_48, Wk_49, Wk_50, Wk_51, Wk_52, Wk_53)
) AS unpvt

This gives me a result of the following: enter image description here

This is going great so far, however I need to add an additional column that says what week # it is. I don't know what for what weeks each row is talking about. It looks like an ASC sort, so I could assume that all rows are weeks 1-53, however I don't want to assume. Is there a way to modify this command to show the week # also?

Upvotes: 0

Views: 125

Answers (2)

Parfait
Parfait

Reputation: 107767

As commented, consider SQL Server's UNPIVOT. However, you will need to source from the Excel file, so consider OPENROWSET if user privileges allow.

SELECT company, salesrep, week, budgetamount
FROM 
   (SELECT company, salesrep, week1, week2, week3, ..., week53
   FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                   'Excel 12.0; Database=C:\Path\To\Excel\File; HDR=YES; IMEX=1', [SheetName$]) e) p
UNPIVOT
   (budgetamount FOR week IN 
      (week1, week2, week3, ..., week53)
) AS unpvt;

Rextester Demo

Upvotes: 2

Adam Jacobson
Adam Jacobson

Reputation: 564

While you can use UNPIVOT, I find cross apply easier to write and understand. (also, if you have more than one column to unpivot, it's easier to expand the code

CREATE table #Budget
(COMPANY INTEGER NOT NULL,
 REP CHAR(10) NOT  NULL,
 WK1 NUMERIC(9,2) NULL,
 wk2 NUMERIC(9,2) NULL,
 wk3 NUMERIC(9,2) NULL,
wk4 NUMERIC(9,2) NULL,
 CONSTRAINT pk_#budget PRIMARY kEY CLUSTERED (Company, REP)
 )
 INSERT INTO #Budget
 (
     COMPANY,
     REP,
     WK1,
     WK2,
     WK3,
     WK4
 )
 VALUES
 (   1, 'ADAM',20,30,40,50),
 (   1, 'JOE',40,50,75,80)

 SELECT bud.COMPANY,
        bud.REP,
        weeks.WkNum,
        weeks.amount
 FROM #Budget bud
 CROSS APPLY 
 (VALUES (1, bud.WK1),
         (2, bud.WK2),
         (3, bud.WK3),
         (4, bud.WK4)
   ) AS weeks(WkNum, amount)
   ORDER BY bud.COMPANY, bud.REP, weeks.WkNum

   DROP TABLE #Budget

Upvotes: 1

Related Questions