Rows to Columns

• ravindra.gangadhar

  SSC Enthusiast

  Points: 122

  More actions

  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.

• Bjorn Pettersen

  SSC-Addicted

  Points: 474

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  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)