Forum Discussion

rocky09's avatar
rocky09
Icon for Solution Sage rankSolution 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.   ActivityID Date InteractionID Type ModifiedBy ...
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

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable 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.

rocky09's avatar
rocky09
Icon for Solution Sage rankSolution Sage
9 years ago

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's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    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