rows into columns

• mnicholas

  SSC Veteran

  Points: 299

  More actions

  October 24, 2011 at 1:58 pm

  #249821

  I'm tring to get results that has first name and last name in a single row rather than column, any help would be appreciated.

  SELECT distinct FIELD_ID,

  CASE WHEN FIELD_ID = 6 THEN FIELD_VALUE ELSE '' END AS [FirstName],

  CASE WHEN FIELD_ID = 7 THEN FIELD_VALUE ELSE '' END AS [LastName]

  From tblTemp

  Current Table:

  FIELD_IDFIELD_VALUEUSER_ID

  7Jones196

  6Tom196

  7West197

  6Dave197

  Desired Results:

  USER_IDFirst_NameLast_Name

  196TomJones

  197DaveWest

  thanks

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  October 24, 2011 at 4:43 pm

  #1398924

  mnicholas (10/24/2011)

  ---

  I'm tring to get results that has first name and last name in a single row rather than column, any help would be appreciated.

  SELECT distinct FIELD_ID,

  CASE WHEN FIELD_ID = 6 THEN FIELD_VALUE ELSE '' END AS [FirstName],

  CASE WHEN FIELD_ID = 7 THEN FIELD_VALUE ELSE '' END AS [LastName]

  From tblTemp

  Current Table:

  FIELD_IDFIELD_VALUEUSER_ID

  7Jones196

  6Tom196

  7West197

  6Dave197

  Desired Results:

  USER_IDFirst_NameLast_Name

  196TomJones

  197DaveWest

  thanks

  You were almost there...

  SELECT [User_ID],

  MAX(CASE WHEN FIELD_ID = 6 THEN FIELD_VALUE ELSE '' END) AS [FirstName],

  MAX(CASE WHEN FIELD_ID = 7 THEN FIELD_VALUE ELSE '' END) AS [LastName]

  FROM tblTemp

  GROUP BY [User_ID]

  ;

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