Transpose Row Data into Column based on a criteria

[rocky09]

I have this below table.  It contains some activities for a Ticket. A Ticket may contains many Activities and each activity has a unique ID.

ActivityID	Date	InteractionID	Type	ModifiedBy
2218163	24-02-2015 20:49	1388956	Email	Steven
2218164	24-02-2015 20:50	1388956	Email	System
2218165	25-02-2015 08:14	1388956	Email	Samuel
2218166	25-02-2015 08:15	1388956	Email	System

Is it possible to place the rows based on a criteria.

The criteria is, where the modifiedby is "System", then that particualr Activity should placed just above the Activity.  See below table, what i am trying to get it.

ActivityID	Date	InteractionID	Type	ModifiedBy	ActivityID	Date	InteractionID	Type	ModifiedBy
2218163	24-02-2015 20:49	1388956	Email	Steven	2218164	24-02-2015 20:50	1388956	Email	System
2218165	25-02-2015 08:14	1388956	Email	Samuel	2218166	25-02-2015 08:15	1388956	Email	System

Any Help is appreciated.

Thank you,

[CahabaData]

will System always be the next sequential record?

[rocky09]

@CahabaData

Yes. System will be the next sequential record.

[CahabaData]

there's more than one way to skin this cat.  since System is always the next sequential plus you have a sequential Activity ID - it gives you alot of choices;

in a formula / measure solution one can employ the EARLIER function

or you can model it at the table level.  if the record set is not super huge I myself prefer the table level but it does use more memory resource.  One could duplicate tables with a filter on ModifiedBy so you have the System Table and the non System Table....  then in the System table create a new calculate column SysActivityID which is ActivityID less 1.   Then join these 2 tables together - that gives you all fields in 1 row at the table level.  As described here these are all steps in the Query Editor which will auto fire each time you refresh data.

[rocky09]

@CahabaData

Good Idea. But, unfortunately, the database is huge and fetching from live sql server. Anyway, I will your suggestion and see the result.

Btw, can we do something in Sql query itself?

[v-qiuyu-msft · Accepted solution]

Hi @rocky09,

You can write the T-SQL query like below:

SELECT
  *
FROM test0825 t 
CROSS APPLY (SELECT TOP 1
  ActivityID ActivityID_,
  Date Date_,
  InteractionID InteractionID_,
  Type Type_,
  ModifiedBy ModifiedBy_
FROM test0825
WHERE ModifiedBy = 'system'
AND InteractionID = t.InteractionID
AND Date >= t.Date
ORDER BY date ASC) ca
WHERE t.ModifiedBy <> 'system'

If you have any question about T-SQL query, please post a thread in Transact-SQL forum.

Best Regards,
QiuyunYu

[rocky09]

@v-qiuyu-msft

Thank you so much.