Here is my situation: I currently have a data table that looks something like this:
Symbol Date Time Sequence Type
---------------------------------------------------------------------------
A 2013.06.06 20:00:00.137 36654383 BestAsk
A 2013.06.06 20:00:00.137 36654383 BestBid
A 2013.06.07 04:00:00.788 3 BestAsk
A 2013.06.07 04:00:00.788 3 BestBid
A 2013.06.07 04:00:00.788 3 BestAsk
A 2013.06.07 04:00:00.788 3 BestBid
A 2013.06.07 07:01:09.584 43126 BestAsk
A 2013.06.07 07:01:09.584 43126 BestBid
A 2013.06.07 07:02:09.012 43431 BestAsk
A 2013.06.07 07:02:09.012 43431 BestBid
As you can tell, the rows come in pairs. Each BestAsk and BestBid row has the same Symbol Date Time and Sequence. Since they are all sorted all nicely, I am thinking the rows can be combined in relation to their order, but all the join commands I looked at only combine columns(aj,aj0,etc). I tried flipping the table into a dictionary and then combining in dictionary form but have been unsuccesful.
What I am looking for in the result is that the identical values Symbol Date Time Sequence don't have to be duplicated but in the Type column, show BestAsk and BestBid :
Symbol Date Time Sequence Type
---------------------------------------------------------------------------
A 2013.06.06 20:00:00.137 36654383 BestAsk,BestBid
A 2013.06.07 04:00:00.788 3 BestAsk,BestBid
A 2013.06.07 04:00:00.788 3 BestAsk,BestBid
A 2013.06.07 07:01:09.584 43126 BestAsk,BestBid
A 2013.06.07 07:02:09.012 43431 BestAsk,BestBid
Something like this, Thank you!
select distinct Type by Symbol,Date,Time,Sequence from table
or use xgroup