Post reply

Rows to Columns

  • December 27, 2007 at 12:52 am

    #181467

    Hi Guys,

    I have table which retrives values and i want to convert those values to columns but the values are not fixed it may increase.

    example

    ID TEXT

    ------------------------

    1 US

    2 UK

    3 SINGAPORE

    etc....

    Required Result.

    US UK SINGAPORE 'ETC.......(It may be around 20 to 30 values max to convert to columns)

    Please advise.

  • December 28, 2007 at 7:12 am

    #763252

    This is the closest I can get. You can extend the list of IDs without corresponding countries.

    (ps: indentation seems to be lost during posting *sigh*)

    declare @country table (

    id int,

    country varchar(15)

    )

    insert into @country values (1, 'US')

    insert into @country values (2, 'UK')

    insert into @country values (3, 'SINGAPORE')

    select

    'country' as id,

    [1], [2], [3], [4]

    from

    (select id, country from @country) as source

    pivot (

    max(country)

    for id in ([1],[2],[3],[4])

    ) as palias

  • December 28, 2007 at 1:04 pm

    #763394

    ... and PLEASE DON'T DOUBLE POST!!!!

    http://www.sqlservercentral.com/Forums/Topic436572-338-1.aspx

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

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

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