How to display months across like crosstab?

  • R.P.Rozema (3/3/2012)


    ? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?

    i had this in my code...albeit m1.m2.m3

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Jeff

    sorry I didnt notice that you were part of the SSC Moderators etc

    Can I eMail you the answer and then you can cut and paste whatever you think best at the appropriate place after you got it working

    Siva

  • I just noticed noticed your article on cross tabs etc.

    I am new to your Forum and didnt notice who is who etc

    There are few additional things i am doing on top of what you are doing, Like Calandar Table, CTE Commands

    since you undertand Pivot better than me I can just send you my solution tomorrow

  • siva 20997 (3/3/2012)


    Jeff

    sorry I didnt notice that you were part of the SSC Moderators etc

    Can I eMail you the answer and then you can cut and paste whatever you think best at the appropriate place after you got it working

    Siva

    I'm not an SSC moderator. Just a concerned fellow programmer.

    You can post your own code. Heh... "after you got it working".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • siva 20997 (3/3/2012)


    I just noticed noticed your article on cross tabs etc.

    I am new to your Forum and didnt notice who is who etc

    There are few additional things i am doing on top of what you are doing, Like Calandar Table, CTE Commands

    since you undertand Pivot better than me I can just send you my solution tomorrow

    It would be better if you just posted it here. All code ideas are welcome and we all learn from other's code.

    I believe that just about anyone can meet the OPS output requirement of listing varying short month names in the output using dynamic SQL . I don't believe there's a way to do the same thing without the use of dynamic SQL which is another of the OPS requirements. If you have a way to do that, then I know I'd certainly like to see it because I'm at a loss for how to do create varying short month names in the output based on the data without the use of dynamic SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • J Livingston SQL (3/3/2012)


    R.P.Rozema (3/3/2012)


    ? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?

    i had this in my code...albeit m1.m2.m3

    I agree. But the OP specifically listed short month names as part of the desired output. I don't believe such a thing can be done without the use of dynamic SQL but would be very happy to see someone pull it off without the use of dynamic SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • siva 20997 (3/3/2012)


    The reply to the original question will be long and hard to understand once done

    hence I just touched upon the subjects which is needed to be understood to undertand the solution

    Since it is quite a big complicated query I will put the whole query in a Strored procedure and return a result set

    The parameter to Sp will specify the starting Date and ending Date. I usually do 12 or 24 months

    I also have Calendar Table. This is so usefull in all aplications I have this as a standard. There is discussion thread on that in this forum

    So the steps involved in the Stored procedure are as follows

    1) select all the rows grouped by Customer and month and the value. you can pick up the month name from the Calendar table after joing it on Date instead of Month Function. easier and faster for grouping etc

    2) Then Pivot the results from 1 above

    to get the results from 1 in to 2 need to use Common Table Expression, which start with the reserved word WITH

    I didnt want to cut and paste my code because it will be difficult to explain but once these are understood maybe

    I agree with all those tools and methods. But none of them will allow for varying months as column headers in the output without a bit of dynamic SQL which the OP isn't allowed to use by the company he works for. If you can think of a way, I, for one, would absolutely love to see it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • J Livingston SQL (3/3/2012)


    R.P.Rozema (3/3/2012)


    ? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?

    i had this in my code...albeit m1.m2.m3

    Ya know, Graham, even though we can't change the column names because dynamic SQL isn't allowed for this problem, using your good code, we could use the first row of the return as a secondary column header. It would require turning the rest of the output to VARCHAR, but it should work.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... {FacePalm}... {Self Head Slap}... {pound head against hard spot on desk}. I've really got to learn to scroll further down in code windows. I didn't see the column renaming you did using sp_Rename. Absolutely brilliant. Like I told Siva, I'd love to see how it could be done and now I have. I've definitely learned something new today. Thanks.

    The only stumbling block is that ALTER permissions are required to use sp_Rename to rename a column. If they don't allow dynamic SQL, they may also seriously restrict such permissions.

    I'm going to go suck my thumb now. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes PIVOT is the best

    CREATE TABLE #Sales (SalesDate smalldatetime,Customer int,Sales decimal(20,4))

    INSERT INTO #Sales Values ('2011/11/01',1,10)

    INSERT INTO #Sales Values ('2011/11/02',1,10)

    INSERT INTO #Sales Values ('2011/05/02',2,25)

    INSERT INTO #Sales Values ('2011/12/01',2,20)

    INSERT INTO #Sales Values ('2012/01/10',3,10)

    INSERT INTO #Sales Values ('2012/02/20',3,10)

    SELECT Customer,

    Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

    FROM

    (

    Select

    left(datename(month,SalesDate ),3) as mn

    ,Sales,Customer

    from

    #Sales

    ) AS SourceTable

    PIVOT

    (

    sum(Sales)

    FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

    ) AS PivotTable;

    DROP TABLE #Sales

  • abhilasht (3/4/2012)


    Yes PIVOT is the best

    CREATE TABLE #Sales (SalesDate smalldatetime,Customer int,Sales decimal(20,4))

    INSERT INTO #Sales Values ('2011/11/01',1,10)

    INSERT INTO #Sales Values ('2011/11/02',1,10)

    INSERT INTO #Sales Values ('2011/05/02',2,25)

    INSERT INTO #Sales Values ('2011/12/01',2,20)

    INSERT INTO #Sales Values ('2012/01/10',3,10)

    INSERT INTO #Sales Values ('2012/02/20',3,10)

    SELECT Customer,

    Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

    FROM

    (

    Select

    left(datename(month,SalesDate ),3) as mn

    ,Sales,Customer

    from

    #Sales

    ) AS SourceTable

    PIVOT

    (

    sum(Sales)

    FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

    ) AS PivotTable;

    DROP TABLE #Sales

    How are you proposing to meet the OP's request...?

    they require Nov/Dec to display before Jan

    Shree-903371 (3/2/2012)


    But when years overlap like if I run for 2011/11/01 to 2012/01/31 then it displays Jan first and then Nov and Dec.

    CustomerJanNovDec

    1102030

    But I need Nov, Dec and then Jan. So can somebody help me?

    CustomerNovDecJan

    1203010

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • The Pivot Table solution given by Livingstone works

    we actually have a table of dates for say from yesr 2000 to 2020 with bank holidays, period number, week number etc etc

    we would have joined the data created by Livingstone to dates table and known every period the summed quantity falls in to

    we would Group it by Account and Period and feed it into Pivot Table method shown by Livinsstone wuing CTE

    So instaead of Jan feb Mar we will have period 111,112,113

    Now in the application dispaly we would have changed 111 to jan 2012 @var1, 112 to Feb 2012 @var2 etc

    I think we do that in the application. we are able to format the titles before display

    With this method the periods can be floating , go over a year boundary, go backwards etc

    However putting the correct title in the TSQL itself with in the Pivot Command I am not if that can be done

    where in that example we have

    FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

    could we have something like

    FOR mn IN (period1 as @var1,Period2 as var@2 etc

    we have not managed to do that

    So our solution would look like

    Ac/No Period1 Period 2 Period3

    A001 100 222 33

    A002 3 44 55

    The application then puts the correct title as it knows what dates belong to period1

  • So we must have the details on the Periods.

    Here i created one sample periods #period table. and come one more issue to find the available periods. So it puts as exec statements.See the sample

    CREATE TABLE #PERIOD (ID INT,DFROM DATE, DTO DATE,PERIODNAME VARCHAR(10))

    DECLARE @STARTDATE DATE,@ENDDATE DATE;

    SELECT @STARTDATE = '2011-01-01',@ENDDATE = '2013-12-31' ;

    WITH CTEDATES AS(

    SELECT TOP (DATEDIFF(MM,@STARTDATE,@ENDDATE) + 1)

    MONTHDATE = DATEADD(MM,

    DATEDIFF(MM,0,@STARTDATE)

    + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)

    FROM SYS.ALL_COLUMNS AC1

    CROSS JOIN SYS.ALL_COLUMNS AC2

    )

    INSERT INTO #PERIOD

    SELECT ROW_NUMBER()OVER(ORDER BY MONTHDATE), MONTHDATE,

    DATEADD(D,-1,DATEADD(MM,1 ,MONTHDATE)),

    SUBSTRING( CONVERT(VARCHAR(100),MONTHDATE,106),4,100)FROM CTEDATES;

    CREATE TABLE #SALES (SALESDATE SMALLDATETIME,CUSTOMER INT,SALES DECIMAL(20,4))

    INSERT INTO #SALES VALUES ('2011/11/01',1,10)

    INSERT INTO #SALES VALUES ('2011/9/01',1,1)

    INSERT INTO #SALES VALUES ('2011/9/01',3,25)

    INSERT INTO #SALES VALUES ('2011/11/02',1,10)

    INSERT INTO #SALES VALUES ('2011/05/02',2,25)

    INSERT INTO #SALES VALUES ('2011/12/01',2,20)

    INSERT INTO #SALES VALUES ('2012/01/10',3,10)

    INSERT INTO #SALES VALUES ('2012/02/20',3,10)

    DECLARE @PERIODS_ORDER VARCHAR(MAX)

    SELECT @PERIODS_ORDER=ISNULL(@PERIODS_ORDER+',','')+'['+PERIODNAME+']' FROM

    #SALES S, #PERIOD P WHERE S.SALESDATE BETWEEN P.DFROM AND P.DTO

    GROUP BY P.ID,PERIODNAME

    DECLARE @sql VARCHAR(MAX)

    SET @sql='

    SELECT CUSTOMER ,

    '+@PERIODS_ORDER+'

    FROM

    (

    SELECT

    PERIODNAME AS MN

    ,SALES,CUSTOMER

    FROM

    #SALES S

    JOIN #PERIOD P ON S.SALESDATE BETWEEN P.DFROM AND P.DTO

    ) AS SOURCETABLE

    PIVOT

    (

    SUM(SALES)

    FOR MN IN ('+@PERIODS_ORDER+')

    ) AS PIVOTTABLE;'

    EXEC (@SQL)

    DROP TABLE #SALES

    DROP TABLE #PERIOD

  • abhilasht (3/4/2012)


    So we must have the details on the Periods.

    Here i created one sample periods #period table. and come one more issue to find the available periods. So it puts as exec statements.See the sample

    we are assuming that

    Shree-903371 (3/2/2012)


    The problem is we are not allowed to use open queries in out campany.

    means OP cannot use dynamic SQL

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • abhilasht (3/4/2012)


    Yes PIVOT is the best

    Actually, it's not. See the article at the following link for why not. Pay particular attention to the performance chart at the end of the article.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 39 total)

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