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.   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

rocky09's avatar
rocky09
Solution 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
Community 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