pivot-unpivot

• shaili

  Right there with Babe

  Points: 753

  More actions

  February 22, 2006 at 2:30 pm

  #116316

  Hi,

  I have a data like this:

  select * from members where member_id = 1

  member_id  first_name last_name ssn

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

  1          Julia      Roberts   123456789

  I need to display data like this:

  member_id 1

  first_name Julia

  Last_name Roberts

  ssn           123456789

  What is the way to do this?

  Thanks,

   

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  February 22, 2006 at 2:42 pm

  #622759

  I would return the data as one row and leave the presentation of the data to the application being used to display the data.

  If, however the data is being presented in a query tool such as Query Analyzer then you have to do something like:

  select 'member_id' ,  member_id

  from members where member_id = 1

  union all

  select 'first_name',  first_name

  from members where member_id = 1

  union all

  select 'last_name',  last_name

  from members where member_id = 1

  union all

  select 'ssn',  ssn

  from members where member_id = 1

  Also, it is a best practice that all table names be in the singular, MEMBER, not plural, MEMBERS, because one row of data represents a MEMBER.

• Jesper-244176

  SSCertifiable

  Points: 7082

  More actions

  February 23, 2006 at 3:26 am

  #622849

  You need to cast to the same datatype to get that working... Something like

  select 'member_id' ,  cast(member_id as varchar(100))

  from members where member_id = 1

  union all

  select 'first_name',  first_name

  from members where member_id = 1

  union all

  select 'last_name',  last_name

  from members where member_id = 1

  union all

  select 'ssn',  cast(ssn as varchar(100))

  from members where member_id = 1

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  February 23, 2006 at 6:28 am

  #622888

  Also, If you use Front End application, you can very well handle this there

  ---

  Madhivanan

  Failing to plan is Planning to fail