Crosstab with changing field order.

Question

Crosstab with changing field order.

Stephen Kinsey

Grasshopper

Points: 17
More actions
July 30, 2004 at 9:58 am

#161290

Hi Everyone,
I have the following crosstab query on my server:
select ReportingType, GroupingField,
    SUM(CASE CalendarPeriod WHEN 1 THEN OTIFPercent ELSE Null END) AS January,
    SUM(CASE CalendarPeriod WHEN 2 THEN OTIFPercent ELSE Null END) AS February,
    SUM(CASE CalendarPeriod WHEN 3 THEN OTIFPercent ELSE Null END) AS March,
    SUM(CASE CalendarPeriod WHEN 4 THEN OTIFPercent ELSE Null END) AS April,
    SUM(CASE CalendarPeriod WHEN 5 THEN OTIFPercent ELSE Null END) AS May,
    SUM(CASE CalendarPeriod WHEN 6 THEN OTIFPercent ELSE Null END) AS June,
    SUM(CASE CalendarPeriod WHEN 7 THEN OTIFPercent ELSE Null END) AS July,
    SUM(CASE CalendarPeriod WHEN 8 THEN OTIFPercent ELSE Null END) AS August,
    SUM(CASE CalendarPeriod WHEN 9 THEN OTIFPercent ELSE Null END) AS September,
    SUM(CASE CalendarPeriod WHEN 10 THEN OTIFPercent ELSE Null END) AS October,
    SUM(CASE CalendarPeriod WHEN 11 THEN OTIFPercent ELSE Null END) AS November,
    SUM(CASE CalendarPeriod WHEN 12 THEN OTIFPercent ELSE Null END) AS December
from TblOTFTemporaryOverallByPeriod
group by ReportingType, GroupingField
This works fine and gives me a nice 12 month crosstab, however I have another requirement that I just can't see how to do. I'd like to a rolling 12 month crosstab going backwards through months. The order of the crosstab would depend on the current calendar month. So for example on the first of august the fields would be ordered August, July June etc... but on the 1st February the fields would go February, January, December etc...
Does anyone know of a way I can do this other than writing 12 difffents stored procedures.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

At first glance, I'm thinking dynamic sql is the other way, but I might be interetsed in 12 procedures instead. Not like they'd change that often.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Hello Stephen,

the only problem is with the column names, but since you didn't explicitly mention that the column names have to be assigned as month names, this SQL could help you. It puts the columns in desired order, first column = current month (CM), each subsequent is one month older (CM-1, CM-2 ...) . I suppose you'll have to play with years somehow, to get the final result, but the basic problem should be solved by this:

create table sql_test (ReportingType varchar(2), GroupingField int, CalendarPeriod int, OTIFPercent int)

insert into sql_test values ('tt', 2, 1, 1)

insert into sql_test values ('tt', 2, 2, 2)

insert into sql_test values ('tt', 2, 3, 3)

insert into sql_test values ('tt', 2, 4, 4)

insert into sql_test values ('tt', 2, 5, 5)

insert into sql_test values ('tt', 2, 6, 6)

insert into sql_test values ('tt', 2, 7, 7)

insert into sql_test values ('tt', 2, 8, 8)

insert into sql_test values ('tt', 2, 9, 9)

insert into sql_test values ('tt', 2, 10, 10)

insert into sql_test values ('tt', 2, 11, 11)

insert into sql_test values ('tt', 2, 12, 12)

declare @cur_period int

select @cur_period = 5 /*this can be passed as a parameter into the SP*/

select ReportingType, GroupingField,

SUM(CASE WHEN CalendarPeriod = @cur_period THEN OTIFPercent ELSE Null END) AS CM,

SUM(CASE WHEN (CalendarPeriod = @cur_period - 1 OR CalendarPeriod = @cur_period + 11) THEN OTIFPercent ELSE Null END) AS [CM-1],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 2 OR CalendarPeriod = @cur_period + 10) THEN OTIFPercent ELSE Null END) AS [CM-2],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 3 OR CalendarPeriod = @cur_period + 9) THEN OTIFPercent ELSE Null END) AS [CM-3],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 4 OR CalendarPeriod = @cur_period + 8) THEN OTIFPercent ELSE Null END) AS [CM-4],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 5 OR CalendarPeriod = @cur_period + 7) THEN OTIFPercent ELSE Null END) AS [CM-5],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 6 OR CalendarPeriod = @cur_period + 6) THEN OTIFPercent ELSE Null END) AS [CM-6],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 7 OR CalendarPeriod = @cur_period + 5) THEN OTIFPercent ELSE Null END) AS [CM-7],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 8 OR CalendarPeriod = @cur_period + 4) THEN OTIFPercent ELSE Null END) AS [CM-8],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 9 OR CalendarPeriod = @cur_period + 3) THEN OTIFPercent ELSE Null END) AS [CM-9],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 10 OR CalendarPeriod = @cur_period + 2) THEN OTIFPercent ELSE Null END) AS [CM-10],

SUM(CASE WHEN (CalendarPeriod = @cur_period - 11 OR CalendarPeriod = @cur_period + 1) THEN OTIFPercent ELSE Null END) AS [CM-11]

from sql_test

group by ReportingType, GroupingField

HTH, Vladan

Oops, hit the Post button too soon. I suppose you will need real month names in your output, but in fact that should be doable either in a front end application, or maybe with another piece of SQL - shouldn't be too complicated. Everything depends on what you want to do with the data. Sorry for running away without a complete answer, but I have to do some work now... bye!

Stephen Kinsey Grasshopper Points: 17 More actions · Answer 3

Apart from the field contents I'd also like the field names in order so that I can display the data on a generic web table (asp.net). I think I'll probably go with 12 stored procedures for now, but I'd really like to find a neater solution.

jain_123 Old Hand Points: 327 More actions · Answer 4

Stephen,

Here is my suggestion, in this solution you will get the moving avarage from current month backwards for 12 months.

select ReportingType, GroupingField,

SUM(CASE month(getdate()) - CalendarPeriod WHEN 0 THEN OTIFPercent ELSE Null END) AS CurrentMonth1,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -1 THEN OTIFPercent ELSE Null END) AS CurrentMonth2,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -2 THEN OTIFPercent ELSE Null END) AS CurrentMonth3,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -3 THEN OTIFPercent ELSE Null END) AS CurrentMonth4,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -4 THEN OTIFPercent ELSE Null END) AS CurrentMonth5,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -5 THEN OTIFPercent ELSE Null END) AS CurrentMonth6,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -6 THEN OTIFPercent ELSE Null END) AS CurrentMonth7,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -7 THEN OTIFPercent ELSE Null END) AS CurrentMonth8,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -8 THEN OTIFPercent ELSE Null END) AS CurrentMonth9,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -9 THEN OTIFPercent ELSE Null END) AS CurrentMonth10,

SUM(CASE month(getdate()) - CalendarPeriod WHEN -10 THEN OTIFPercent ELSE Null END) AS CurrentMonth11,

SUM(CASE month(getdate()) - CalendarPeriod WHEN 11 THEN OTIFPercent ELSE Null END) AS CurrentMonth12

from TblOTFTemporaryOverallByPeriod

group by ReportingType, GroupingField

Have a lot of fun

Anand.

steved SSC Rookie Points: 40 More actions · Answer 5

For easy solutions to all kinds of crosstab/pivoting problems check out the RAC utility.Similar in concept to Access crosstab but much more powerful with many options/features.Fully integrated to sql server as RAC is a system of server sp's and functions.In addition, RAC easily simulates many of the analytic functions that (hopefully) will be in Yukon,ie. row_number().rank,dense_rank etc.This makes RAC an alternative general problem solving tool as analytic functions are much easier to grasp than completed and convuluted sql89/92.

Note that for crosstabs/pivoting RAC does not use heaps of CASE statements nor CURSORS

RAC v2.2 and QALite @

http://www.rac4sql.net

Check out RAC at:
www.angelfire.com/ny4/rac/