Forum Discussion

rocky09's avatar
rocky09
Solution Sage
9 years ago
Solved

Transpose Row Data into Column based on a criteria

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.

 

ActivityIDDateInteractionIDTypeModifiedBy
221816324-02-2015 20:491388956EmailSteven
221816424-02-2015 20:501388956EmailSystem
221816525-02-2015 08:141388956EmailSamuel
221816625-02-2015 08:151388956EmailSystem

 

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.

 

ActivityIDDateInteractionIDTypeModifiedByActivityIDDateInteractionIDTypeModifiedBy
221816324-02-2015 20:491388956EmailSteven221816424-02-2015 20:501388956EmailSystem
221816525-02-2015 08:141388956EmailSamuel221816625-02-2015 08:151388956EmailSystem

 

Any Help is appreciated.

 

Thank you,

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

6 Replies

      • CahabaData's avatar
        CahabaData
        Memorable Member
        9 years ago

        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.