Post reply

Name columns for a pivot

  • February 13, 2020 at 9:06 pm

    Go to Answer

    #3725765

    I want to put this comment column in 1 row.  I have a pivot that is working but I don't know how to name the columns "comment1", "comment2", "comment3".  Can you please help.

    The data:

    SQL code:

    Select * 
    from MeritusMmPoComments 
    Pivot (max(Comment) 
    		FOR CommentSeqID in ([1],[2],[3],[4]))  As pvt

    My pivot results:

     

  • February 13, 2020 at 10:47 pm

    #3725778 

    Select SourceId, PurchasOrderID, AppearsOn, RowUpdateDateTime, 
           [1] comment1,
           [2] comment2,
           [3] comment3
      from MeritusMmPoComments 
     Pivot (max(Comment) 
       FOR CommentSeqID in ([1],[2],[3],[4])) As pvt

  • February 14, 2020 at 8:46 pm

    #3726052

    That worked...thanks!

    I have a separate related issue now.

    The data looks like this

    When I run this code:

    Select SourceID, PurchaseOrderID, [1] as Comment1, [2] as Comment2, [3] as Comment3, [4] as Comment4  
    			from  MeritusMmPoComments
    			Pivot (max(Comment) 
    			FOR CommentSeqID in ([1],[2],[3],[4]))  As pvt
    			where PurchaseOrderID = '494819' and sourceid = 'WAC'

    The results look like this:

    I was expecting Comment3 to be on line 1 and not have 2 rows.  But I think RowUpdateDateTime column is causing this issue.  How can i get this to be on 1 row and not 2?

     

  • February 14, 2020 at 8:52 pm

    #3726054

    If you give an example of the input you have and the output you expect I might be able to help. From your description I can't really work out what you want the output to be.

  • February 14, 2020 at 8:58 pm

    #3726058

    This is the input data:

    This is what I would like it to look like:

     

  • February 14, 2020 at 9:36 pm

    #3726079

    Couldn't you just write it without PIVOT like this?

    ;with MeritusMmPoComments as
    (
        Select * FROM (VALUES
        ('WAC', 494819,1,'ALL','Joe Fleichman/81-2035','2017-08-30 13:14:00'),
        ('WAC', 494819,2,'ALL','VISA: $183.95','2017-08-30 13:14:00'),
        ('WAC', 494819,3,'ALL','ORDER# 98168','2017-08-30 13:14:00')) T(SourceID,PurchaseOrderID,CommentSeqID,AppearsOn,Comment,RowUpdateDateTime)
    )
    SELECT x.SourceID,
           x.PurchaseOrderID,
           x.AppearsOn,
           MAX(IIF(x.CommentSeqID=1,x.Comment,NULL)) Comment1,
           MAX(IIF(x.CommentSeqID=2,x.Comment,NULL)) Comment2,
           MAX(IIF(x.CommentSeqID=3,x.Comment,NULL)) Comment3,
           MAX(IIF(x.CommentSeqID=4,x.Comment,NULL)) Comment4
    FROM MeritusMmPoComments x
    GROUP BY x.SourceID,x.PurchaseOrderID,x.AppearsOn

  • February 14, 2020 at 9:42 pm

    #3726080

    I think it works better with the pivot because I am joining it to create a new table...

    Into POInfo 
    From MeritusMmPO as PO 
    Left Join MeritusMmPOLines as POL 
    	ON PO.SourceID = POL.SourceID AND
    		PO.PurchaseOrderID = POL.PurchaseOrderID  
    Left Join (Select SourceID, PurchaseOrderID, [1] as Comment1, [2] as Comment2, [3] as Comment3, [4] as Comment4  
    			from MeritusMmPoComments 
    			Pivot (max(Comment) 
    			FOR CommentSeqID in ([1],[2],[3],[4]))  As pvt) as POC
    	ON PO.SourceID = POC.SourceID AND 
    		PO.PurchaseOrderID = POC.PurchaseOrderID
    Where Cast(PO.OrderDateTime as Date) >= '12/01/2016'

  • February 14, 2020 at 10:29 pm

    Answer

    #3726088

    Just put it in an OUTER APPLY:

    Into POInfo
    From MeritusMmPO as PO 
    Left Join MeritusMmPOLines as POL 
    	ON PO.SourceID = POL.SourceID AND
    		PO.PurchaseOrderID = POL.PurchaseOrderID  
    OUTER APPLY(SELECT POC.SourceID,
                       POC.PurchaseOrderID,
                       POC.AppearsOn,
                       MAX(IIF(POC.CommentSeqID=1,POC.Comment,NULL)) Comment1,
                       MAX(IIF(POC.CommentSeqID=2,POC.Comment,NULL)) Comment2,
                       MAX(IIF(POC.CommentSeqID=3,POC.Comment,NULL)) Comment3,
                       MAX(IIF(POC.CommentSeqID=4,POC.Comment,NULL)) Comment4
                  FROM MeritusMmPoComments POC
                 WHERE PO.SourceID = POC.SourceID 
                   AND PO.PurchaseOrderID = POC.PurchaseOrderID
                 GROUP BY POC.SourceID,POC.PurchaseOrderID,POC.AppearsOn) as POC		
    Where Cast(PO.OrderDateTime as Date) >= '12/01/2016'

  • February 16, 2020 at 1:41 am

    #3726216

    Jonathan, This works great!  thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply