Merge two records to one

[ba543]

Hi I have a special query thats been requested of me and it invloves a single table that is producing a repetative result. What Im looking to do is merge the repetative records reducing the redundant data to one record and any columns that are not redundant be given their own column alias.

For example this is the table result with two of the same node_Names but as you can see the Event_time is different as well as the Event_Type:

node_Name IP_Address Event_Time Event_Type
CISC0 1 206.12.33.33 3:00PM 1
CISCO 1 206.12.33.33 5:00PM 5

I would like to see the final output to be merged like the following:

node_Name IP_Address Event_Time1 Event_Time5
CISC0 1 206.12.33.33 3:00PM 5:00 PM


Note: We are looking to query Event_Type 1 and 5 and their times associated to a single record and then also produce a duration being (Event_Time1 - Event_Time2).

Your help would be greatly appreciated.

 

[Pampers]

I got this result after using 2 query's. Maybe you can use it:

node_name IP_Address 1 5 dif
CISC0 1 206.12.33.33 15:00 17:00 -120

first query
TRANSFORM Max(tblNodeEvent.Event_Time) AS MaxOfEvent_Time
SELECT tblNodeEvent.node_name, tblNodeEvent.IP_Address
FROM tblNodeEvent
GROUP BY tblNodeEvent.node_name, tblNodeEvent.IP_Address
PIVOT tblNodeEvent.Event_Type;

second query
SELECT Query1.node_name, Query1.IP_Address, Query1.[1], Query1.[5], DateDiff("n",[5],[1]) AS dif
FROM Query1;

Pampers
Just let it go...