SQL Pivot no aggregate

Question

I am looking to pivot a data set result out but there is no aggregate that is happening.

Select StufferID from from #temp_Stuffers where SponsorID IN (111,222,333)

This is going to give me 0 - 2 results. How can I use pivot to make it

Sponsor ID    StufferID1   StufferID2
111            S1           S2
222            S5
333

Rather than

SponsorID     StufferID
111           S1
111           S2
222           S5

Is there a maximum number of stuffers per sponsor? You'll need to use an `outer join` instead of `in`. And if you don't know the maximum number of stuffers, you'll need to use `dynamic sql` as well. — sgeddes, Jun 22 '16 at 22:18

score 1 · Answer 1 · answered Jun 22 '16 at 22:25

You need to use an outer join to get your desired results -- in won't work correctly. So first move your where criteria to a join. Then you'll need to establish some field to pivot on, this uses row_number:

select s.sponsorid, 
       max(case when t.rn = 1 then t.stufferid end) stufferid1,
       max(case when t.rn = 2 then t.stufferid end) stufferid2
from (select 111 as sponsorid union all select 222 union all select 333) s
    left join (
        select *, row_number() over 
                      (partition by sponsorid order by stufferid) rn 
        from #temp_Stuffers) t on s.sponsorid = t.sponsorid
group by s.sponsorid

If you don't know the maximum number of potential stufferid values, then you'll need to use dynamic sql as well.

score 1 · Accepted Answer · answered Jun 22 '16 at 22:58

You can use a left join to the table as itself using an alias to find Min and Max values as each column. You'll need to slightly modify this to work in SQL Server as I'm using Oracle.

SELECT TEMP_STUFFERS.SPONSORID, 
       MIN(TEMP_STUFFERS.STUFFERID)  AS STUFFERID1,
       MAX(TEMP_STUFFERS2.STUFFERID) AS STUFFERID2
  FROM TEMP_STUFFERS 
  LEFT JOIN TEMP_STUFFERS TEMP_STUFFERS2 
         ON TEMP_STUFFERS.SPONSORID  = TEMP_STUFFERS2.SPONSORID
        AND TEMP_STUFFERS.STUFFERID != TEMP_STUFFERS2.STUFFERID
 WHERE TEMP_STUFFERS.SPONSORID IN (111,222,333)
 GROUP BY TEMP_STUFFERS.SPONSORID
 ORDER BY TEMP_STUFFERS.SPONSORID;

Not a pivot but it works just fine. Thank you. And I can always do something to expand the sponsor IDs.. — 10thTiger, Jun 24 '16 at 21:40

SQL Pivot no aggregate

2 Answers2