crosstab

  • Hello,

    I have a table like

    Yr      C1      C2    C3   C4       C..        Cn

    2000    2        4     5     6          7        8

    2001     4       5     3      9          0       10

    .

    200n     2      4     4        4          4     7   

     

    I want to convert it to

    Column     2000      2001   .  200n

    C1           2           4           2

    C2          4            5           10

    C3

    C4

    .

    Cn

    What will be the best way to do this conversion?

    Thanks for the help.

     

  • -- prepare test data

    declare @test table (Yr int, C1 int, C2 int, C3 int, C4 int, C5 int, C6 int)

    insert @test

    select 2000, 2, 4, 5, 6, 7, 8 union all

    select 2001, 4, 5, 3, 9, 0, 10 union all

    select 2003, 2, 4, 4, 4, 4, 7

    -- do the work

    select  'c1' [column],

      max(case when yr = 2000 then c1 end) [2000],

      max(case when yr = 2001 then c1 end) [2001],

      max(case when yr = 2003 then c1 end) [2003]

    from  @test

    union all

    select  'c2',

      max(case when yr = 2000 then c2 end),

      max(case when yr = 2001 then c2 end),

      max(case when yr = 2003 then c2 end)

    from  @test

    union all

    select  'c3',

      max(case when yr = 2000 then c3 end),

      max(case when yr = 2001 then c3 end),

      max(case when yr = 2003 then c3 end)

    from  @test

    union all

    select  'c4',

      max(case when yr = 2000 then c4 end),

      max(case when yr = 2001 then c4 end),

      max(case when yr = 2003 then c4 end)

    from  @test

    union all

    select  'c5',

      max(case when yr = 2000 then c5 end),

      max(case when yr = 2001 then c5 end),

      max(case when yr = 2003 then c5 end)

    from  @test

    union all

    select  'c6',

      max(case when yr = 2000 then c6 end),

      max(case when yr = 2001 then c6 end),

      max(case when yr = 2003 then c6 end)

    from  @test


    N 56°04'39.16"
    E 12°55'05.25"

  • Thanks a lot ,

     if the  Yr will be variable like it varies the range based on the return , how that can be handled

    for example the above example is for three years and if the next query will be from 1995 to 2000.

     

    Thanks

  • select  'c1' [column],

      max(case when yr = 1995 then c1 end) [1995],

      max(case when yr = 1996 then c1 end) [1996],

      max(case when yr = 1997 then c1 end) [1997],

      max(case when yr = 1998 then c1 end) [1998],

      max(case when yr = 1999 then c1 end) [1999],

      max(case when yr = 2000 then c1 end) [2000]

    from  @test-2

    union all

    select  'c2',

      max(case when yr = 1995 then c2 end),

      max(case when yr = 1996 then c2 end),

      max(case when yr = 1997 then c2 end),

      max(case when yr = 1998 then c2 end),

      max(case when yr = 1999 then c2 end),

      max(case when yr = 2000 then c2 end)

    from  @test-2

    union all

    select  'c3',

      max(case when yr = 1995 then c3 end),

      max(case when yr = 1996 then c3 end),

      max(case when yr = 1997 then c3 end),

      max(case when yr = 1998 then c3 end),

      max(case when yr = 1999 then c3 end),

      max(case when yr = 2000 then c3 end)

    from  @test-2

    union all

    select  'c4',

      max(case when yr = 1995 then c4 end),

      max(case when yr = 1996 then c4 end),

      max(case when yr = 1997 then c4 end),

      max(case when yr = 1998 then c4 end),

      max(case when yr = 1999 then c4 end),

      max(case when yr = 2000 then c4 end)

    from  @test-2

    union all

    select  'c5',

      max(case when yr = 1995 then c5 end),

      max(case when yr = 1996 then c5 end),

      max(case when yr = 1997 then c5 end),

      max(case when yr = 1998 then c5 end),

      max(case when yr = 1999 then c5 end),

      max(case when yr = 2000 then c5 end)

    from  @test-2

    union all

    select  'c6',

      max(case when yr = 1995 then c6 end),

      max(case when yr = 1996 then c6 end),

      max(case when yr = 1997 then c6 end),

      max(case when yr = 1998 then c6 end),

      max(case when yr = 1999 then c6 end),

      max(case when yr = 2000 then c6 end)

    from  @test-2

     

    Or you can read my article at http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

     


    N 56°04'39.16"
    E 12°55'05.25"

  • With all due respect to the efforts of Mr. Peter Larrson the be all and end all for dynamic crosstabs in ms sql server is the RAC utility @: http://www.rac4sql.net

    It combines the flexibility to solve complex problems with simplicity. There is simply no more reason to reinvent the wheel nor waste time in trying code complex crosstab problems. Since RAC is a set of t-sql sp's and a few functions its integrated with engine for even further flexibility. Just imagine the simplicity of Access combined with the power of a server. And you will find much more than xtabs in RAC. RAC offers functionality like olap functions row_number, rank() and dense_rank(). And it goes further with additional ranking options not available in S2005.

    Best,

    http://racster.blogspot.com/

     

  • Are you suggesting that RAC is better than Reporting Services (which is free, by the way)?

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

  • The problem which I have is to make the Year as a parmeter in the stored procedure?

     

    Thanks,

     

Viewing 7 posts - 1 through 6 (of 6 total)

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