How to Pivot Without Aggregation

Question

I've been reading on this site as well as Google about how to Pivot my data but can't seem to quite figure it out. I want to be able to pivot without doing any aggregation. My data currently looks like. My sql is:

select act, rn
from ##Temp_Outage_Crew
order by act

act         rn
00208763-BC2    AUXO01
00208763-BC2    AUXO03

But I want it to look like what is shown below. I now there is a way to PIVOT this data but I can't get it to work.

act             rn1        rn2
00208763-BC2    AUXO01     AUX03

I have "attempted" to use the row number function but I can't seem to get that to work either.

The only reason not to aggregate is when there's only value; in such a case using the `max` function on that value would return the value; so though you'd be using aggregation, you'd get the result you want. — JohnLBevan, Feb 23 '16 at 19:01
Possible duplicate of [TSQL Pivot without aggregate function](http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Tab Alleman, Feb 23 '16 at 19:01

score 2 · Answer 1 · edited May 23 '17 at 11:59

As mentioned in the comments, you must use an aggregation in the PIVOT. MAX works on strings, so it's a great option when you have a 1-to-1 relationship.

To get the arbitrary rn1 and rn2 columns, it makes sense to use ROW_NUMBER(). Assuming you know the maximum number of values rn can have for a particular act value, you can do something like:

;WITH toc_CTE
     AS (SELECT [act],
                [rn],
                [RowNum] = CONCAT('rn', ROW_NUMBER()
                                          OVER (
                                            PARTITION BY [act]
                                            ORDER BY [rn]))
         FROM   #Temp_Outage_Crew)
SELECT [act],
       [rn1],
       [rn2],
       [rn3],
       [rn4]
FROM   toc_CTE
       PIVOT (MAX([rn])
             FOR [RowNum] IN ([rn1],
                              [rn2],
                              [rn3],
                              [rn4])) pvt

If, however, there is no concrete limit on the number of rn values per act, then you will have to use a dynamic PIVOT.

That did it. I just couldn't figure out the query syntax. I had tried multiple variations with no luck but the syntax above worked perfectly. Thank you so much! — Lisa Rose, Feb 23 '16 at 19:19
@Lisa welcome to Stack Overflow. If this answer solved your problem satisfactorily, please consider "accepting" it using the check mark next to the score. This lets other users on the site know the question has a viable solution. — CactusCake, Feb 23 '16 at 20:01

How to Pivot Without Aggregation

1 Answers1