Need help in this query

  • hi

    i have my input table as follows

    ID----total_amt-----total_month--------start_date

    1-----1200000--------6-------------- 9/4/2009

    2----300000---------3---------------- 1/1/2006

    3----300000---------10--------------- 9/6/2006

    4

    5 ...so on

    and i want a way to get the output as

    ID

    -----sept'09----oct'09----nov'09-----dec'09-----total for 2009---jan'10-----feb10----Tot

    1---200000---200000---200000---200000----- 800000------ 200000----200000--

    -----jan'06----- feb'06-----mar'06-----Total for 2006

    2-----100000----100000-----100000----300000

    divide the amount as per the total number of months

    I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.

  • sql.abhishek (9/15/2010)


    I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.

    For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.

    For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/15/2010)


    sql.abhishek (9/15/2010)


    I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.

    For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.

    For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.

    For how to get the DDL/insert code, please read the first two links in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (9/15/2010)


    Craig Farrell (9/15/2010)


    sql.abhishek (9/15/2010)


    I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.

    For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.

    For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.

    For how to get the DDL/insert code, please read the first two links in my signature.

    I give in. Wayne, I'm stealin' yer links. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • The requested output is not really something you should expect from a SQL statement:

    a) It seems like you want to have "flexible column names" for each row which is totally against any kind of relational database concept. A table has a column with a name and a value per row.

    b) It also seems you're looking for a flexible number of columns holding the same type of information (e.g. Total for ID) in different columns depending on the value of the original column total_month.

    You might want to rethink the requirement you're looking for. To split data in the way you describe shouldn't be done using T-SQL. It's a front end task. What you could do on the SQL side is a query to return ID, column number, column name and column value. At the front end, design a layout to get the column name and related column value per ID ordered by column number.

    It is possible to do it using T-SQL (even including a blank row between each statement). But that would be like banging a nail into a wall with a screwdriver: possible, but not recommended. Get the right tool for the job! 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • @ WayneS

    Thanks a lot for the links ... I really appreciate ur help ..

    i think i can start working on these lines and i may get the desired results.

    Regards...

  • Craig Farrell (9/15/2010)


    I give in. Wayne, I'm stealin' yer links. 🙂

    No problem. We all have them because it's just so much easier on us...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • sql.abhishek (9/15/2010)


    @ WayneS

    Thanks a lot for the links ... I really appreciate ur help ..

    i think i can start working on these lines and i may get the desired results.

    Regards...

    You might want to also check out the links in my signature for CROSS-TABS/Pivots, Part 1 and Part 2.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • sql.abhishek (9/15/2010)


    hi

    i have my input table as follows

    ID----total_amt-----total_month--------start_date

    1-----1200000--------6-------------- 9/4/2009

    2----300000---------3---------------- 1/1/2006

    3----300000---------10--------------- 9/6/2006

    4

    5 ...so on

    and i want a way to get the output as

    ID

    -----sept'09----oct'09----nov'09-----dec'09-----total for 2009---jan'10-----feb10----Tot

    1---200000---200000---200000---200000----- 800000------ 200000----200000--

    -----jan'06----- feb'06-----mar'06-----Total for 2006

    2-----100000----100000-----100000----300000

    divide the amount as per the total number of months

    I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.

    The others are correct about the links on how to provide data. That, notwithstanding, please see the following link for a coded example of what you want to do. Wayne points to the same link, as well. http://www.sqlservercentral.com/Forums/Topic986540-391-1.aspx

    --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)

  • LutzM (9/15/2010)


    The requested output is not really something you should expect from a SQL statement:

    a) It seems like you want to have "flexible column names" for each row which is totally against any kind of relational database concept. A table has a column with a name and a value per row.

    b) It also seems you're looking for a flexible number of columns holding the same type of information (e.g. Total for ID) in different columns depending on the value of the original column total_month.

    You might want to rethink the requirement you're looking for. To split data in the way you describe shouldn't be done using T-SQL. It's a front end task. What you could do on the SQL side is a query to return ID, column number, column name and column value. At the front end, design a layout to get the column name and related column value per ID ordered by column number.

    It is possible to do it using T-SQL (even including a blank row between each statement). But that would be like banging a nail into a wall with a screwdriver: possible, but not recommended. Get the right tool for the job! 😉

    I absolutely agree with what you say but consider the following... what if there is no "front end" for this task?? 😉 Remember... to a nail, everything is a hammer. 😛

    --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)

  • Jeff Moden (9/15/2010)


    LutzM (9/15/2010)


    ...

    I absolutely agree with what you say but consider the following... what if there is no "front end" for this task?? 😉 Remember... to a nail, everything is a hammer. 😛

    Assuming I'd be forced to come up with a result in a format as requested but without any front end app, I'd probably do it this way (for a static solution and as a starting point to have something to turn into dynamic sql)

    DECLARE @tbl TABLE

    (

    ID INT,

    total_amt INT,

    total_month INT,

    start_date DATETIME

    )

    INSERT INTO @tbl

    SELECT 1,1200000,6,'9/14/2009' UNION ALL

    SELECT 2, 300000,3,'1/1/2006' UNION ALL

    SELECT 3, 300000,10,'9/6/2006'

    ;

    WITH cte AS

    (

    SELECT

    ID,

    N+1 AS Pos,

    STUFF(CONVERT(CHAR(11),DATEADD(mm,N,start_date),0),4,6,'''') AS col1,

    total_amt/total_month AS amt_mnth,

    total_amt

    FROM @tbl t

    CROSS APPLY

    (

    SELECT number N

    FROM master..spt_values

    WHERE TYPE='P'

    AND number < total_month

    )x

    )

    SELECT

    CASE WHEN grp=3 THEN '' ELSE CAST(ID AS VARCHAR(10)) END AS ID_,

    -- one sample row to show the nest level of the CASE statements

    MAX(

    CASE

    WHEN Pos=1 THEN

    CASE

    WHEN grp= 1 THEN col1

    WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10))

    ELSE ''

    END

    ELSE ''

    END) AS [ ],

    MAX(CASE WHEN Pos=2 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=3 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=4 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=5 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=6 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=7 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=8 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=9 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=10 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ]

    FROM cte

    CROSS APPLY

    (

    SELECT 1 AS grp UNION ALL -- display the month

    SELECT 2 UNION ALL -- display the value per month

    SELECT 3 -- empty row

    ) y

    GROUP BY ID,grp

    ORDER BY ID,grp

    /* result set

    ID

    1Sep'09Oct'09Nov'09Dec'09Jan'10Feb'10

    1200000200000200000200000200000200000

    2Jan'06Feb'06Mar'06

    2100000100000100000

    3Sep'06Oct'06Nov'06Dec'06Jan'07Feb'07Mar'07Apr'07May'07Jun'07

    330000300003000030000300003000030000300003000030000

    */



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • You can make if stamment

    IF (SELECT COUNT(Months) FROM tablename

    after

    declare the amount of each months (you make specific that some months are different amounts)

    begin and end store procedure...should be work fine...

  • ... and here's the version to include the total amount right after the last month, based on the sample posted before.

    ;

    WITH cte AS

    (

    SELECT

    ID,

    N+1 AS Pos,

    STUFF(CONVERT(CHAR(11),DATEADD(mm,N,start_date),0),4,6,'''') AS col1,

    total_amt/total_month AS amt_mnth

    FROM @tbl t

    CROSS APPLY

    (

    SELECT number N

    FROM master..spt_values

    WHERE TYPE='P'

    AND number < total_month

    )x

    UNION ALL

    SELECT

    ID,

    total_month + 1 AS Pos,

    'total' AS col1,

    total_amt AS amt_mnth

    FROM @tbl t

    )

    SELECT

    CASE WHEN grp=3 THEN '' ELSE CAST(ID AS VARCHAR(10)) END AS ID_,

    -- one sample row to show the nest level of the CASE statements

    MAX(

    CASE

    WHEN Pos=1 THEN

    CASE

    WHEN grp= 1 THEN col1

    WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10))

    ELSE ''

    END

    ELSE ''

    END) AS [ ],

    MAX(CASE WHEN Pos=2 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=3 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=4 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=5 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=6 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=7 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=8 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=9 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=10 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],

    MAX(CASE WHEN Pos=11 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ]

    FROM cte

    CROSS APPLY

    (

    SELECT 1 AS grp UNION ALL -- display the month

    SELECT 2 UNION ALL -- display the value per month

    SELECT 3 -- empty row

    ) y

    GROUP BY ID,grp

    ORDER BY ID,grp



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • @sql.abhishek,

    So! Are you all set or what? If you're all set, would you mind posting your final solution so that others may learn, please? Thanks.

    --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)

  • Hey Lutz,

    Thanx alot for the solution...

    I am trying to put my exact problem into your solution and will see how it works ......

    Regards,

    abhisheka

Viewing 15 posts - 1 through 15 (of 16 total)

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