Post reply

Pivot table in SQL SERVER 2008

  • April 29, 2009 at 9:33 am

    #208762

    Hi All,

    I need to pivot a table from rows to columns in SQL Server 2008.

    The example below will show you what I would like to do. The first query just gives the way the results are stored in the table and the second query shows the way I would like to see the results finally. I was trying to see if I can use the pivot functionality but have been unable to do so.

    I donot want to do a union all like I have done in the second query to get the desired format, primarily because the number of columns in the actual table is variable eg I have used ColValue1 and Colvalue2 but in the actual table there are variable number of columns.

    Any help will be greatly appreciated. 🙂

    DECLARE @TestData TABLE

    ( PeriodId INT

    ,ColValue1 INT

    ,ColValue2 INT)

    INSERT @TestData

    SELECT 200901, 11, 12 UNION ALL

    SELECT 200902, 21, 22 UNION ALL

    SELECT 200903, 31, 32 UNION ALL

    SELECT 200904, 41, 42

    SELECT * FROM @TestData

    -- Desired Format

    SELECT 'ColValue1', 11 as [200901], 21 as [200902], 31 as [200903], 41 as [200904] UNION ALL

    SELECT 'ColValue2', 12 as [200901], 22 as [200902], 32 as [200903], 42 as [200904]

  • April 29, 2009 at 9:36 am

    #986044

    http://www.simple-talk.com has an article on dynamic pivots in SQL.

    Personally, I always recommend pivoting in the front-end application. Excel, Crystal, SSRS, are all better at dynamic pivots than T-SQL is.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 29, 2009 at 9:40 am

    #986048

    Thanks GSquared, there is no front end to the app that i need to use it in.

    It is a reporting services report that will be manually run by the user on a need by need basis.

    However I can try and fix the number of columns in the result if that will help making the pivot simple.

    Thanks again for the help.

  • April 29, 2009 at 9:44 am

    #986051

    If it's a reporting services report, then reporting services is the front end.

    If you really need to do the pivot in SQL, then take a look at the article on simple-talk.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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