Consolidating multiple columns to a single record

• Bob Boursaw

  SSC Eights!

  Points: 865

  More actions

  April 1, 2008 at 9:57 pm

  #184672

  This is probably an easy one but I'm stumped.

  I have the following table from a call routing system.

  TASK_ID MSG_TYPE APPL_ID QTIME HIME ATIME

  1234 CON 15 NULL NULL NULL

  1234 DISC NULL 104 NULL

  1234 QUE NULL NULL NULL3

  1234 NOT NULL 42NULL

  Sorry for my table being a bit whacky, but I think you'll get the idea.

  Basically, there are 4 records in the table which have information about a single call. Some of the call information is in the record with a MSG_TYPE of CON while other information for this same call is in records of other message types (all with the same TASK_ID).

  My goal is to retrieve a single record for each unique task_id that has all of the associated call information.

  The result set would look something like...

  TASK_ID, APPL_ID, QTIME, HTIME, ATIME

  1234 15 15 42 3

  1235 .....

  1236 .......

  I keep trying various things within my where clause but I can't seem to get the result I'm looking for.

  Any help would be greatly appreciated.

  Bob

• HanShi

  SSC-Dedicated

  Points: 33519

  More actions

  April 2, 2008 at 5:26 am

  #797106

  Hi Bob,

  Assuming there will be only one value in eacht column (per task_ID) you can use it with the GROUP BY clause.

  The query will be like this:

  SELECT TASK_ID, MAX(APPL_ID), MAX(QTIME), MAX(HIME), MAX(ATIME)

  FROM TableName

  GROUP BY TASK_ID

  Regards,

  Hans

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **