Pivot, Unpivot, Cross Apply?

Question

Say I have data like the below and what I really want is to (pivot?) AssigedType as columns (Handling, Supervising, etc.) although I don't think pivot is the right way to go as I'm not aggregating anything. There'll be three tables involved: Matters, MattersProfessionals, Professionals), i.e.

Matters.Matters = MattersProfessionals.Matters Professionals.Professionals = Matters.Professionals

matterid    AssignedType    ProfName
27391-0001001   Handling    Skip A. Lawyer
37085-0051001   Handling    Skip A. Lawyer
37085-0051001   Supervising Skip A. Lawyer
18814-0226WO1   Handling    Skip A. Lawyer
37085-0050001   Handling    Skip A. Lawyer
37085-0050001   Supervising Skip A. Lawyer
37085-0053001   Supervising Skip A. Lawyer
37085-0053001   Handling    Skip A. Lawyer
37085-0052001   Handling    Skip A. Lawyer
37085-0052001   Supervising Skip A. Lawyer
18814-0226AU1   Handling    Skip A. Lawyer

Desired output:

MatterID       Handling        Supervising 
27391-0001001  Skip A. Lawyer  Skip A.Lawyer 
etc...

Like a pivot but without the aggregation?

Where the value of AssignedType is a column name, and where ProfName is the value, along side MatterID (Matters.MatterID) — Steve, Apr 28 '15 at 15:04
That doesn't make much sense. Can you try to explain that more clearly? — Sean Lange, Apr 28 '15 at 15:12
Using the data I supplied as an example: Columns: MatterID, Handling, Supervising Row Values: 27391-0001001, Skip A. Lawyer, Skip A.Lawyer Like a pivot but without the aggregation? — Steve, Apr 28 '15 at 15:16
possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — jpw, Apr 28 '15 at 15:18
What you want is some kind of pivot transformation. The post I marked as duplicate applies to your question. — jpw, Apr 28 '15 at 15:19
Aggregates can be MAX or MIN too. They don't have to be SUM. — Sean Lange, Apr 28 '15 at 15:21
@StevenMpls Yes, but you can use `max()` to aggregate and chose the non-null value. The highest voted answer in the linked question shows this. — jpw, Apr 28 '15 at 15:22

score 0 · Answer 1 · answered Apr 28 '15 at 15:22

Min() or Max() can also be aggregations for your pivot function:

SELECT * from (<here the original select goes>) 
AS source
PIVOT 
    (min(ProfName) For AssignedType in ([Handling], [Supervising])) 
AS y;

This would return a table like

matterid       Handling        Supervising     
-------------  --------------  --------------  
27391-0001001  Skip A. Lawyer  (null)          
37085-0051001  Skip A. Lawyer  Skip A. Lawyer  
...

Pivot, Unpivot, Cross Apply?

1 Answers1