Post reply

show row in columns problem

  • January 13, 2011 at 6:25 am

    #230380

    hi

    I have table as:

    Id Product TT Week

    1 Test1 75 1

    2 Test2 20 1

    3 Test1 60 2

    4 Test2 40 2

    5 Test1 35 3

    6 Test2 65 3

    . ...... ... ..

    . ...... ... ..

    . ...... ... ..

    And I want to show it as

    Product week1 week2 week3 week4 ... .... ...

    Test 1 75 60 35 .. .. ..

    Test 2 25 40 65 .. .. ..

    please help me how to get this by query

  • January 13, 2011 at 6:58 am

    #1273002

    DECLARE @table AS TABLE(Id INT, Product CHAR(5), TT INT, Week CHAR(2))

    INSERT INTO @table

    SELECT 1, 'Test1', 75, '1'

    UNION ALL SELECT 2, 'Test2', 20, '1'

    UNION ALL SELECT 3, 'Test1', 60, '2'

    UNION ALL SELECT 4, 'Test2', 40, '2'

    UNION ALL SELECT 5, 'Test1', 35, '3'

    UNION ALL SELECT 6, 'Test2', 65, '3'

    SELECT pivoteddata.*

    FROM (SELECT product, tt, week

    FROM @table) AS rawdata

    PIVOT (MAX(tt) FOR week IN ([1], [2], [3])) AS pivoteddata

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 13, 2011 at 7:16 am

    #1273016

    also, check out the cross-tab/pivot table articles (Parts 1 & 2) linked to in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 14, 2011 at 12:10 am

    #1273417

    thanks for reply.

    but in this case week column in result not fixed.

  • January 14, 2011 at 12:24 am

    #1273422

    rsanuj (1/14/2011)

    thanks for reply.

    but in this case week column in result not fixed.

    In this case the CrossTab articles Wayne pointed you at will help you to resolve the issue. Especially the 2nd part (DynamicCrossTab).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 5 posts - 1 through 5 (of 5 total)

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