Name columns for a pivot

Question

Post reply

Name columns for a pivot

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
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:

Jonathan AC Roberts

SSCoach

Points: 18354

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'

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

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

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

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 2

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?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

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.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 4

This is the input data:

This is what I would like it to look like:

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

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

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 6

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'

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 7

GrassHopper

SSCarpal Tunnel

Points: 4473

February 16, 2020 at 1:41 am

#3726216

Jonathan, This works great! thanks!