help to summarise

  • I have a table in the following format:

    headings: code year1 year2 year3

    values: A1 250.00 null null

    A1 null 150.00 null

    A1 null null 180.00

    I want to create a summarised table with one single row: I.E

    A1 250.00 150.00 180.00

    Another problem is that I never know how many years of account there are, so the ability to have a dynamic query would be best for me.

    Any help or suggestions would be gratefully received.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • SET NOCOUNT ON

    DECLARE @MyAccts TABLE

    (

    Account VARCHAR(10),

    Year1   NUMERIC(10,2) NULL,

    Year2   NUMERIC(10,2) NULL,

    Year3   NUMERIC(10,2) NULL

    )

    INSERT @MyAccts

    SELECT 'A1', 250.00, null, null UNION

    SELECT 'A1', null, 150.00, null UNION

    SELECT 'A1', null, null, 180.00

    SELECT SUM(COALESCE(Year1, 0)) Year1, SUM(COALESCE(Year2, 0)) Year2, SUM(COALESCE(Year3, 0)) Year3

    FROM

     @MyAccts

    Regards,
    gova

  • /*I would make a design something like this*/

    SET NOCOUNT ON

    /* Data Table */

    CREATE TABLE MyAcctsNew

    (

    Account VARCHAR(10),

    DateYear DATETIME,

    Amount NUMERIC(10,2)

    )

    INSERT MyAcctsNew

    SELECT 'A', '01/01/2000', 250.00 UNION

    SELECT 'A', '01/01/2001', 150.00 UNION

    SELECT 'A', '01/01/2002', 180.00 UNION

    SELECT 'A', '01/01/2003', 185.00

    /* Solution */

    DECLARE @Years TABLE

    (

    AcctYear VARCHAR(4)

    )

    INSERT @Years

    SELECT DISTINCT CONVERT(VARCHAR, DATEPART(YEAR, DateYear)) FROM MyAcctsNew

    DECLARE @SQL VARCHAR(2000)

    SELECT @SQL = 'SELECT Account' + CHAR(13)

    SELECT @SQL = COALESCE(@SQL + ', ', '') + 'Year' + AcctYear + ' = SUM(CASE WHEN DATEPART(YEAR, DateYear) = ' + AcctYear + ' THEN Amount ELSE 0 END)' + CHAR(13)

    FROM

     (SELECT TOP 100 PERCENT AcctYear

      FROM

      @Years

      ORDER BY 1) A

    SELECT @SQL = @SQL + 'FROM ' + CHAR(13)

    SELECT @SQL = @SQL + '     MyAcctsNew ' + CHAR(13)

    SELECT @SQL = @SQL + 'GROUP BY Account' + CHAR(13)

    EXECUTE(@SQL)

    GO

    DROP TABLE MyAcctsNew

    Regards,
    gova

  • Thanks, govinn. Just what the doctor ordered.

    Simplifies it for me a lot.


    ------------------------------
    The Users are always right - when I'm not wrong!

Viewing 4 posts - 1 through 3 (of 3 total)

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