Results 1 to 5 of 5

Thread: Pivot in T-sql

  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Pivot in T-sql

    "TRANSFORM Sum(tblClaims.[CurrentLossReserve]) AS SumOfCurrentLossReserve SELECT tblClaims.[LossStatCode], tblClaims.[ClaimCode], Sum(tblClaims.[CurrentLossReserve]) AS [Total Of CurrentLossReserve] FROM tblClaims GROUP BY tblClaims.[LossStatCode], tblClaims.[ClaimCode] PIVOT Format([DateReport],'yyyy-mm'); "



    how to convert this code to t-sql this runs in msaccess

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    It's a bit hard to understand what you are really trying to achieve without some more detailed explanation. However using my best guess I come out some suggestion for you so that you can try using my script as below.

    1. Create test table:
    CREATE TABLE tblClaims (
    LossStatCode int
    , ClaimCode varchar(10)
    , CurrentLossReserve int
    , DateReport DateTime)

    2. Create test data:
    -- 1,a1
    INSERT INTO tblClaims VALUES (1,'a1',3,'2009-10-01')
    INSERT INTO tblClaims VALUES (1,'a1',4,'2009-10-02')
    INSERT INTO tblClaims VALUES (1,'a1',5,'2009-10-03')
    INSERT INTO tblClaims VALUES (1,'a1',6,'2010-01-01')
    INSERT INTO tblClaims VALUES (1,'a1',3,'2010-01-02')
    INSERT INTO tblClaims VALUES (1,'a1',4,'2010-01-03')
    -- 1,a2
    INSERT INTO tblClaims VALUES (1,'a2',13,'2009-10-01')
    INSERT INTO tblClaims VALUES (1,'a2',14,'2009-11-01')
    INSERT INTO tblClaims VALUES (1,'a2',15,'2009-11-05')
    INSERT INTO tblClaims VALUES (1,'a2',16,'2010-02-01')
    INSERT INTO tblClaims VALUES (1,'a2',13,'2010-02-05')
    INSERT INTO tblClaims VALUES (1,'a2',14,'2010-02-09')
    -- 1,a3
    INSERT INTO tblClaims VALUES (1,'a3',23,'2009-12-01')
    INSERT INTO tblClaims VALUES (1,'a3',24,'2009-12-07')
    INSERT INTO tblClaims VALUES (1,'a3',25,'2009-12-21')
    INSERT INTO tblClaims VALUES (1,'a3',26,'2010-03-01')
    INSERT INTO tblClaims VALUES (1,'a3',23,'2010-03-09')
    INSERT INTO tblClaims VALUES (1,'a3',24,'2010-03-11')

    3. Script to review:
    SELECT LossStatCode,Convert(varchar(7),DateReport,121) [ReportMonth], a1 AS a1, a2 AS a2, a3 AS a3
    FROM
    (SELECT CurrentLossReserve, ClaimCode, LossStatCode, Convert(varchar(7),DateReport,121) [DateReport]
    FROM tblClaims) p
    PIVOT
    (
    SUM (CurrentLossReserve)
    FOR ClaimCode IN
    ( a1,a2,a3)
    ) AS pvt
    ORDER BY LossStatCode

    4. Test result:
    LossStat ReportMonth
    Code a1 a2 a3
    ------------ ----------- ----------- ----------- -----------
    1 2009-10 12 13 NULL
    1 2009-11 NULL 29 NULL
    1 2009-12 NULL NULL 72
    1 2010-01 13 NULL NULL
    1 2010-02 NULL 43 NULL
    1 2010-03 NULL NULL 73


    Try yourself even if this is not exactly what you wanted by changing columns a bit.

  3. #3
    Join Date
    Aug 2009
    Posts
    73
    Well if i don't have any limit on number of claim code...like i don't know its a1,a2,a3 there can be many..so what's the resolution for that....as in my access query which i originally posted i am usind date part as to be displayed as column and there can be many

  4. #4
    Join Date
    Feb 2007
    Posts
    38
    It seems like you want to pivot on the Month instead of ClaimCode.

    Then tweak the original script to the following script.
    SELECT LossStatCode,ClaimCode,[2009-10] as Month0910,[2009-11] as Month0911,[2009-12] as Month0912,[2010-01] as Month1001,[2010-02] as Month1002
    FROM
    (SELECT CurrentLossReserve, ClaimCode, LossStatCode, Convert(varchar(7),DateReport,121) [YYYYMM]
    FROM tblClaims) p
    PIVOT
    (
    SUM (CurrentLossReserve)
    FOR [YYYYMM] IN
    ( [2009-10],[2009-11],[2009-12],[2010-01],[2010-02],[2010-03])
    ) AS pvt
    ORDER BY LossStatCode

    As the count of YYYYMM is limited and known, you may not have problem typing the target month range.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Best option is not to do this in T-SQL - do this in your presentation layer. Most presentation tools can pivot data easily.
    If you are determined to do this in T-SQL (for some reason everyone seems to be) you can use Terry's suggestions. If you do not know all the values you need to pivot on then you now need to write some dynamic sql to build a csv string of the values and insert this into your pivot statement.

    If you want more information about why pivoting is not a good idea in SQL try googling for some of Celko's responses on the subject - he is rather forthright on the matter.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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