October 10, 2005 at 1:46 pm
Hi,
I have an excel file which contains the sales data for certain restaurants. The excel file contains columns
store sep-week4 sep-week3 sep-week2 sep-week1 aug-week4 .....jan-week1.
It has sales information for each store for all the weeks of year 2005 until september. Now I have a table in the sql server database which has columns
store, week, totalSales. I need to compare the totalSales that is in this table with the sales from that excel sheet. If there is a way to convert the excel data into the format store, week, totalsales then I could compare them easily. But I am not knowing how to get the excel data in this format. Please let me know if you have any ideas.
Thanks,
Sridhar.
October 10, 2005 at 3:14 pm
Hi Sridhar,
There are two ways to tackle this problem - one from the SQL side and the other from the Excel side.
From the SQL Server side, you can use a cross tab query to return the rows in your table as columns like:
SELECT Restaurant,
SUM(CASE Week WHEN 'sep-week4' THEN Total ELSE 0 END) AS 'sep-week4',
SUM(CASE Week WHEN 'sep-week3' THEN Total ELSE 0 END) AS 'sep-week3', ...
SUM(CASE Week WHEN 'jan-week1' THEN Total ELSE 0 END) AS 'jan-week1'
GROUP BY Restaurant
assuming your column names in your totals by week table are Restaurant, Week, and Total (for the total sales for a particular restaurant in a particular week).
From the Excel side, I've written the following Macro to transpose row data into column data. You just highlight the cells that contain the data you want to transpose (including the column headings) and run the macro. In your case, you'll want to highlight the week columns, but not the restaurant column.
The macro will place the transposed data in the two columns directly to the right of the highlighted cells. If all goes well, you can then just delete the columns with the original weekly sales data.
This comes with no guarantee or support. There is no error checking, so if your transpose exceeds the number of rows permitted in Excel, it will just blow up. Any data in the two columns immediately to the right of your selection will be overwritten, and there's no "Undo" capability, so save your spreadsheet before running.
Sub Transpose()
'
' Transpose Macro
' Perform transpose of crosstab data to vector format
' Select the values to be transposed including the headings for the columns
' Two columns will be appended - the first will have the column heading and the second will have the value
'
' Created by Keith Myers
' 08/03/2005
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim iHeaderRow As Integer
Dim iRow As Integer
Dim iStartCol As Integer
Dim iCols As Integer
Dim iIterations As Integer
Dim iCount As Integer
iHeaderRow = Selection.Row
iRow = iHeaderRow + 1
iStartCol = Selection.Column
iCols = Selection.Columns.Count
iIterations = Selection.Rows.Count - 1
iCount = 1
While iCount <= iIterations
' Insert Rows
Range(Rows(iRow + 1), Rows(iRow + iCols - 1)).Select
Selection.Insert shift:=xlDown
' Copy Headings to new column
Range(Cells(iHeaderRow, iStartCol), Cells(iHeaderRow, iStartCol + iCols - 1)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(iRow, iStartCol + iCols).Select
Selection.PasteSpecial Transpose:=True
'Copy value row to new column
Range(Cells(iRow, iStartCol), Cells(iRow, iStartCol + iCols - 1)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(iRow, iStartCol + iCols + 1).Select
Selection.PasteSpecial Transpose:=True
' Copy Leading values down on new rows
Range(Cells(iRow, 1), Cells(iRow, iStartCol - 1)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(iRow, 1), Cells(iRow + iCols - 1, iStartCol - 1)).Select
ActiveSheet.Paste
'Increment Counters
iRow = iRow + iCols
iCount = iCount + 1
Wend
End Sub
Thanks,
Keith
October 10, 2005 at 3:41 pm
Hi Keith,
I would like to try the sql option but I didn't understand exactly what you said. I have a table called store_sales. In that it has columns
store, sep-week4, sep-week3, sep-week2, sep-week1, aug-week4, aug-week3 etc. Now I want to convert this table into a table which has columns store, week and sales. please let me know how to do it.
Thanks,
Sridhar.
October 11, 2005 at 12:29 pm
Keith's SQL will output this
| store | sep-week3 | sep-week4 | ...etc |
| A | 100.00 | 0.00 | ...etc |
| B | 0.00 | 50.00 | ...etc |
Isn't that what you needed?
October 11, 2005 at 1:21 pm
No I need the output in the format
store week sales
A sep-week3 100.00
B sep-week4 0.00
...
...
October 11, 2005 at 3:21 pm
SET NOCOUNT ON
DECLARE @OldTable TABLE
(
StoreID INT,
WeekA NUMERIC(10,2),
WeekB NUMERIC(10,2),
WeekC NUMERIC(10,2),
WeekD NUMERIC(10,2),
WeekE NUMERIC(10,2)
)
INSERT INTO @OldTable
SELECT 1, 23.4, 43.5, 23.6, 45.6, 56.7 UNION
SELECT 2, 24.4, 73.5, 73.6, 45.7, 16.7 UNION
SELECT 3, 25.4, 63.5, 27.6, 43.6, 46.7
DECLARE @NewTable TABLE
(
StroreID INT,
WeekID VARCHAR(10), -- Iwould keep this as date
Sales NUMERIC(10,2)
)
INSERT @NewTable
SELECT StoreID, 'WeekA', WeekA FROM @OldTable UNION
SELECT StoreID, 'WeekB', WeekB FROM @OldTable UNION
SELECT StoreID, 'WeekC', WeekC FROM @OldTable UNION
SELECT StoreID, 'WeekD', WeekD FROM @OldTable UNION
SELECT StoreID, 'WeekE', WeekE FROM @OldTable
SELECT * FROM @NewTable
Regards,
gova
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply