Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Pivot table Query.... Problem Please Help

    i have a tblclaims with following columns
    a is varchar...b is int datereport is datetime
    a-------b-------datereport


    qq------12 ---- 2007-2-23

    as------344-----2007-3-30

    asq-----588-----2008-1-1

    qwer-----848----2008-1-4
    ksja------32------2009-2-2

    i want output to be pivoted on datereprt with following columns to be displayed with corresponding data

    a-------b-------2007-2-----2007-3-----2008-1------2009-2

    *******************all data***********************


    i wrote following query as datereport column is getting inserts everyday

    DECLARE
    @columns VARCHAR(8000)
    select @columns= COALESCE(@columns + ',[' + left( CONVERT(VARCHAR(10), datereport, 120),7 ) + ']',
    '[' +left( CONVERT(VARCHAR(10), datereport, 120),7 )+ ']')
    FROM tblClaims
    GROUP BY left( CONVERT(VARCHAR(10), datereport, 120),7 ) order by left( CONVERT(VARCHAR(10), datereport, 120),7 )

    DECLARE @query VARCHAR(8000)
    SET @query = '
    SELECT *
    FROM tblclaims
    PIVOT
    (
    sum(b)
    FOR [Datereport]
    IN ('
    + @columns + ')
    )
    AS p'
    EXECUTE(@query)


    its throwing me and error as i only want to pivot YYYY-MM ............pls help!!!!!!!!!!!!!!!!
    Last edited by amitwadhawan123; 04-25-10 at 16:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •