Select multiple rows into columns - dynamic pivot

Question

Here is what the initial query returns:

plan_id   Rep_id     rep_nm         employee_id    Row_id
6720      35         Robert Jones   160            1
6720      36         Pam Smith      23             2
6720      37         Erik Johnson   85             3
6720      38         Sally Ells     212            4
6719      40         Barbara Wax    168            5

I need to get the rows to be as follows:

plan_id  Rep_id1 rep_nm1      emp_id1  Rep_id2  rep_nm2   emp_id2 Rep_id3  etc.
6720     35      Robert Jones 160      36       Pam Smith 23      Erik Johnson
6719     40      Barbara Wax  168      NULL     NULL      NULL    NULL    

The maximum rep_id* columns will be 5. I tried searching for a pivot query for such an example but no success.

Answer 1

You did not specify what RDBMS you are using but if you are using SQL server, then you can implement both the UNPIVOT and PIVOT functions. If you know how many values you are going then you can hard-code the values:

select *
from
(
  select plan_id, value, col+cast(rn as varchar(10)) col
  from
  (
    select plan_id, 
      cast(rep_id as varchar(12)) rep_id, 
      rep_nm, 
      cast(employee_id as varchar(12)) employee_id, 
      row_number() over(partition by plan_id order by rep_id) rn
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (rep_id, rep_nm, employee_id)
  ) up
) un
pivot
(
  max(value)
  for col in ([rep_id1], [rep_nm1], [employee_id1], 
              [rep_id2], [rep_nm2], [employee_id2],
              [rep_id3], [rep_nm3], [employee_id3],
              [rep_id4], [rep_nm4], [employee_id4])
) piv

See SQL Fiddle with Demo

But if you have an unknown number of values, then you can use dynamic SQL similar to this:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable') and
               C.name not in ('plan_id', 'Rep_id', 'Row_id')
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name 
                         + cast(t.rn as varchar(10)))
                    from
                    (
                      select row_number() over(partition by plan_id order by rep_id) rn
                      from yourtable
                    ) t
                    cross apply sys.columns as C
                   where C.object_id = object_id('yourtable') and
                         C.name not in ('plan_id', 'Rep_id', 'Row_id')
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select *
      from
      (
        select plan_id, value, col+cast(rn as varchar(10)) col
        from 
        (
          select plan_id, 
            cast(rep_id as varchar(12)) rep_id, 
            rep_nm, 
            cast(employee_id as varchar(12)) employee_id, 
            row_number() over(partition by plan_id order by rep_id) rn
          from yourtable
        ) x
        unpivot
        (
          value
          for col in ('+ @colsunpivot +')
        ) u
      ) src
      pivot
      (
        max(value)
        for col in ('+ @colspivot +')
      ) p'

exec(@query)

See SQL Fiddle with Demo

Both will produce the same result:

| PLAN_ID | EMPLOYEE_ID1 |      REP_NM1 | EMPLOYEE_ID2 |   REP_NM2 | EMPLOYEE_ID3 |      REP_NM3 | EMPLOYEE_ID4 |    REP_NM4 |
------------------------------------------------------------------------------------------------------------------------------
|    6719 |          168 |  Barbara Wax |       (null) |    (null) |       (null) |       (null) |       (null) |     (null) |
|    6720 |          160 | Robert Jones |           23 | Pam Smith |           85 | Erik Johnson |          212 | Sally Ells |