Transform columns into rows

  • 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.

  • 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

  • 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.

  • 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?

  • No I need the output in the format

    store week sales

    A sep-week3 100.00

    B sep-week4 0.00

    ...

    ...

  • 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