Post reply

Convert Row values to Columns

  • December 17, 2007 at 3:14 am

    #179999

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

    Please advise for a single query without using any temp tables,SP's or functions others...

    Please advise.

  • December 17, 2007 at 5:32 am

    #760655

    Since you're using SQL 2005, look up Pivot in books online.

    Does exactly what you're looking for.

    p.s. Why the restriction on not using temp tables, functions, etc?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 17, 2007 at 5:48 am

    #760658

    ravindra.gangadhar (12/17/2007)

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

    Please advise for a single query without using any temp tables,SP's or functions others...

    Please advise.

    So you want to see 10K columns if there are 10K rows? [;)]

    Madhivanan

    Failing to plan is Planning to fail

  • December 17, 2007 at 7:24 pm

    #760883

    Yes, If 10K rows then i want 10K columns. Thanks

  • December 17, 2007 at 8:08 pm

    #760888

    That's going to be difficult - SQL2005 only allows for 1024 columns (in 32-bit) or 4096 (in 64-bit) per table, and 4096 per select statement.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • December 17, 2007 at 9:12 pm

    #760893

    The closest you're going to get is this tool,

    http://www.geckoware.com.au/Content.aspx?Doc_id=1002

    If that doesn't meet your needs then its time to re-evaluate your desired outcomes.

    I mean really, who is going to look a 10K columns ... :ermm::unsure:

    --------------------
    Colt 45 - the original point and click interface

  • December 17, 2007 at 9:54 pm

    #760895

    Hi

    Using privot is a good idea. since the number of columns can change your pivot query can be a dynamic sql.

    "Keep Trying"

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

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