Transpose rows to columns by row number

• h7432379

  SSC Enthusiast

  Points: 182

  More actions

  November 5, 2018 at 3:11 am

  #378552

  Hi,
  I have my main table (let's say, Ticket), that contains all informations about every ticket handled in the system.
  This table has 3 columns (Note1, Note2, Note3), that I need to fill with the results from another query, that retrieves the last 3 annotations (in date decreasing order) that have been attached to the ticket.

  This is the query that allows me to retrieve the annotations (these are linked to the ticket through the "Regarding" field).
  

  So in my Ticket table I should see the content of "Contenuto" in Note1, Note2 and Note3.

  I don't know if rownumb is necessary to accomplish what I want.

  I've seen most of examples use te PIVOT function, but it's usually used to aggregate for example by the sum for a certain ID, and that's not what I need: I just need to retrieve the first 3 rows returned by a query and fill Note1, Note2 and Note3 columns in the Ticket table.

  If you need, this is the query to retrieve the last 3 attachments:

  
select top 3 concat(subject, ' | ', content, ' | ', creazione) contenuto,
creazione,
regarding,
ROW_NUMBER() OVER(ORDER BY creazione desc) rownumb

                       from
                     
                     
                     (
select top 3
'Email' as Tipo,
a.RegardingObjectId as Regarding,
a."subject" as "Subject",
null as "Content",
a.CreatedOn as creazione
from MyDB.dbo.ActivityPointerBase a
where a.ActivityTypeCode = 4202
union
select top 3
'Note' as Tipo,
ann.ObjectId as Regarding,
ann."subject" as "Subject",
ann.NoteText as "Content",
ann.CreatedOn as creazione
from MyDB.dbo.AnnotationBase ann
) as Act
where Regarding = 'D1AC2989-7BD4-E811-80EC-0050568C9BE1'
order by creazione desc

  Obviously in this sample query the "Regarding" is hardcoded to retrieve only the attachments for one of my Tickets
  Thanks in advance

• @Taps

  SSC Eights!

  Points: 879

  More actions

  November 7, 2018 at 7:21 am

  #2012685

  Hi
  You can still use the pivot function with 'MAX'
  A rough example can be:
  

  select * from #temp

  --contentscreate_timeidrownum
--sjdhfkdsa2018-11-04 14:05:42.230a112
--kjfkdsjgk2018-11-02 14:05:58.357a113
--abcdjfkgqa2018-11-06 14:05:01.187a111

  select id , max([1]) , max([2]) , max([3]) from 
    (select * from #temp ) t 
     pivot ( 
          max(contents) for rownum in ( [1] , [2] , [3])) as pvt
group by id

  
  Thanks