asked on

Reformat Data with a View or Crosstab Query or SP?

How could I create a View (using something like a Crosstab Query) on MS SQL Server 2000 to get the format of data that is needed as shown below? If a View cannot do so, how could I use something else like an SP to achieve the goal as described below?

Here is the scenario:

1. I have a table 'tblSales' that records data on a DAILY basis (you can assume regularly used data type):

RecordID      LocationID Date_Sales Sales_Type_1 Sales_Type_2 Sales_Type_3 Cost_1 Cost_2 Cost_3
1      1      1/31/2003        10        15        20        5       10 15
2      1      2/20/2003        20        25        30        15       20 25
3      1      3/31/2003        30        35        40        25       30 35
4      1      4/30/2003        15        20        25        10       15 20
5      1      5/31/2003        45        50        55        40       45 50
6      1      6/3/2003        50        55        60        45       50 55
7      1      7/3/2003        40        45        50        35       40 45
8      1      8/3/2003        30        35        40        25       30 35
9      1      9/3/2003        60        65        70        55       60 65
10      1      10/3/2003        60        65        70        55        60 65
11      1      11/5/2003        70        75        80        65        70 75
12      1      12/31/2003        80        85        90        75        80 85
13      1      1/31/2004        10        15        20        5        10 15
14      1      2/20/2004        20        25        30        15        20 25
15      1      3/31/2004        30        35        40        25        30 35
16      1      4/30/2004        15        20        25        10        15 20
17      1      5/31/2004        45        50        55        40        45 50
18      1      6/3/2004        50        55        60        45        50 55
19      1      7/3/2004        40        45        50        35        40 45
20      1      8/3/2004        30        35        40        25        30 35
21      1      9/3/2004        60        65        70        55        60 65
22      1      10/3/2004        60        65        70        55        60 65
23      1      11/5/2004        70        75        80        65        70 75
24      1      12/31/2004        80        85        90        75        80 85
25      2      1/31/2003        10        15        20        5        10 15
26      2      2/20/2003        20        25        30        15        20 25
27      2      3/31/2003        30        35        40        25        30 35
28      2      4/30/2003        15        20        25        10        15 20
29      2      5/31/2003        45        50        55        40        45 50
30      2      6/3/2003        50        55        60        45        50 55
31      2      7/3/2003        40        45        50        35        40 45
32      2      8/3/2003        30        35        40        25        30 35
33      2      9/3/2003        60        65        70        55        60 65
34      2      10/3/2003        60        65        70        55        60 65
35      2      11/5/2003        70        75        80        65        70 75
36      2      12/31/2003        80        85        90        75        80 85
37      2      1/31/2004        10        15        20        5        10 15
38      2      2/20/2004        20        25        30        15        20 25
39      2      3/31/2004        30        35        40        25        30 35
40      2      4/30/2004        15        20        25        10        15 20
41      2      5/31/2004        45        50        55        40        45 50
42      2      6/3/2004        50        55        60        45        50 55
43      2      7/3/2004        40        45        50        35        40 45
44      2      8/3/2004        30        35        40        25        30 35
45      2      9/3/2004        60        65        70        55        60 65
46      2      10/3/2004        60        65        70        55        60 65
47      2      11/5/2004        70        75        80        65        70 75
48      2      12/31/2004        80        85        90        75        80 85

2. What I need is a View or something like a View that shows such a different format as the following:

Please note:
(1) A tricky part is data needs to be sorted by LocationID, then FISCAL MONTH, arranged horizontally by FISCAL YEAR which runs from October in the current calendar year to September in the next calendar year;
(2) The date range used in the above table can be ANY and the data by month below should be summation of data in each month in the above within appropriate FISCAL YEAR;
(3) The Row Name used below should be generated dynamically based on data range used (such as FY_2005_XXX, FY_2006_XXX, etc.).

LocationID Month FY_2004_Sales_Type_1 FY_2004_Sales_Type_2 FY_2004_Sales_Type_3 FY_2004_Cost_1 FY_2004_Cost_2 FY_2004_Cost_3
1        Oct        60        65        70        55        60        65
1        Nov        70        75        80        65        70        75
1        Dec        80        85        90        75        80        85
1        Jan        10        15        20        5        10        15
1        Feb        20        25        30        15        20        25
1        Mar        30        35        40        25        30        35
1        Apr        15        20        25        10        15        20
1        May        45        50        55        40        45        50
1        Jun        50        55        60        45        50        55
1        Jul        40        45        50        35        40        45
1        Aug        30        35        40        25        30        35
1        Sep        60        65        70        55        60        65
2        Oct        60        65        70        55        60        65
2        Nov        70        75        80        65        70        75
2        Dec        80        85        90        75        80        85
2        Jan        10        15        20        5        10        15
2        Feb        20        25        30        15        20        25
2        Mar        30        35        40        25        30        35
2        Apr        15        20        25        10        15        20
2        May        45        50        55        40        45        50
2        Jun        50        55        60        45        50        55
2        Jul        40        45        50        35        40        45
2        Aug        30        35        40        25        30        35
2        Sep        60        65        70        55        60        65

Your help would be greatly appreciated and rewarded!

yli

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yongshu Li

ASKER

That's a very good example to handle data with CALENDAR year horizontally. My major problem is FISCAL MONTH and FISACL YEAR. Could you find another example similiar to my case?

Thank you.

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yongshu Li

ASKER

Thank you and let me test this one.

Yongshu Li

ASKER

Hi, Lowfatspread:

Hope you had a great weekend!

The above modification works very well, except that I need the following changes:

1. How could I add another parameter in the sp_TRANSFORM as a boolean type to show or hide "TOTAL" etc.. In my case, I don't want the "Total" to show at all, but I'd like to keep the flexibility when I need it.

In the original SP, it uses 6 parameters as follows:

CREATE PROCEDURE sp_TRANSFORM

@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@DEBUG bit = 0

AS
........

I'd like to add one more parameter like '@Show_Aggregate_Function bit = 0' as default to make it look like the following:

CREATE PROCEDURE sp_TRANSFORM

@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@Show_Aggregate_Function bit = 0, -- suggested new param.
@DEBUG bit = 0

AS
........

But I'm not sure how to modify the sp to make it work.

2. In my View, I added another Column to be used as 'ORDER BY'. Let's call it 'Fiscal_Month_Order' so that Fiscal month 'October' will be always the first and September will be the last. For instance, I modified your code as follows:

create view sales_fiscal
as

SELECT TOP 100 PERCENT MONTH(sales_date) AS Fiscal_Month_Numeric
       , SUBSTRING(DATENAME(mm,sales_date),1,3) AS Fiscal_Month_Text
       , CASE WHEN MONTH(sales_date) = 10 THEN 1
             WHEN MONTH(sales_date) = 11 THEN 2
              WHEN MONTH(sales_date) = 12 THEN 3
             WHEN MONTH(sales_date) = 1 THEN 4
             WHEN MONTH(sales_date) = 2 THEN 5
             WHEN MONTH(sales_date) = 3 THEN 6
             WHEN MONTH(sales_date) = 4 THEN 7
             WHEN MONTH(sales_date) = 5 THEN 8
             WHEN MONTH(sales_date) = 6 THEN 9
             WHEN MONTH(sales_date) = 7 THEN 10
             WHEN MONTH(sales_date) = 8 THEN 11
             WHEN MONTH(sales_date) = 9 THEN 12 END AS Fiscal_Month_Order
       , CASE WHEN MONTH(sales_date) > 9 THEN YEAR(sales_date) + 1 ELSE YEAR(sales_date) END AS Fiscal_Year
       , SUBSTRING(DATENAME(mm,sales_date),1,3) + '_' + CONVERT(CHAR(4), CASE WHEN MONTH(sales_date) > 9 THEN YEAR(sales_date) + 1 ELSE YEAR(DataDate) END) AS Fiscal_Period
       , S.*

From Sales

ORDER BY CASE WHEN MONTH(sales_date) > 9 THEN YEAR(sales_date) + 1 ELSE YEAR(sales_date) END
       , CASE WHEN MONTH(sales_date) = 10 THEN 1
              WHEN MONTH(sales_date) = 11 THEN 2
              WHEN MONTH(sales_date) = 12 THEN 3
              WHEN MONTH(sales_date) = 1 THEN 4
              WHEN MONTH(sales_date) = 2 THEN 5
              WHEN MONTH(sales_date) = 3 THEN 6
              WHEN MONTH(sales_date) = 4 THEN 7
              WHEN MONTH(sales_date) = 5 THEN 8
              WHEN MONTH(sales_date) = 6 THEN 9
              WHEN MONTH(sales_date) = 7 THEN 10
              WHEN MONTH(sales_date) = 8 THEN 11
              WHEN MONTH(sales_date) = 9 THEN 12 END

This works fine in my View, but won't in the Crosstab Query when even using the ORIGINAL SP_TRANSFORM, as it will be sorted by the @Select_Column (In my case, which is 'Fiscal_Month_Text'. And it will be sorted alphabetically like 'Apr', 'Aug', 'Dec', ...SEP. This is NOT what I want. It should be in the order of FISCAL MONTH: 'Oct,' 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep'.

If I have to modify the original sp, I'd like to have 8 parameters as follows:

CREATE PROCEDURE sp_TRANSFORM

@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Order_Column nvarchar(255), -- Suggested new param, Used to sort by. Should be flexible as it could be the same as the @Select_Column.
@Pivot_Column nvarchar(255),
@Show_Aggregate_Function bit = 0, -- Suggested new param, used to show/hide the "Total" etc.
@DEBUG bit = 0

AS
........

How could I make it work? Would you, if possible, provide complete REVISED coding based on the original sp_Transform at http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp?

Thank you!!!

Yongshu Li

ASKER

Anybody else who could help? It'd be really appreciated if someone could meet the challenge :) :)