Stored procedure to do a two-level pivot

Question

I am trying to create a stored procedure in Sybase Adaptive Server Anywhere that will do a double pivot of table. I will first outline with some images what I am trying to accomplish.

The problem

1. Raw data

Here is the raw data in the table; in the sample code that I have posted lower down this is temporary table #t1 :

2. First level of pivoting

The first level of pivoting involves grouping on the column rownr and pivoting on the column col, putting the resulting table into temporary table #t2 :

I have the code up to this point which I have posted lower down.

3. Second level of pivoting

This is the section that I am struggling with. I am now needing to pivot table #t2 grouping on the column ObjectId and replicating the columns Operation and Code for the number of rows in the grouping to produce table #t3. So the result for the example I have given would look like this:

Because two columns are being replicated (Operation and Code) the number of columns in the resulting table should equal 2 multiplied by the number of rows in the grouping with the largest number of rows. Groupings that have less than the maximum number of grouped rows will be padded with null values, as seen in the example.

The code

Here is my code that creates the first two tables, #t1 and #t2 :

begin
    create table #t1(rownr int, col nvarchar(15), val nvarchar(300));
    insert into #t1 values(1, 'ObjectId', 'A');
    insert into #t1 values(1, 'Operation', 'Op1');
    insert into #t1 values(1, 'Code', '101');
    insert into #t1 values(2, 'ObjectId', 'A');
    insert into #t1 values(2, 'Operation', 'Op2');
    insert into #t1 values(2, 'Code', '102');
    insert into #t1 values(3, 'ObjectId', 'B');
    insert into #t1 values(3, 'Operation', 'Op3');
    insert into #t1 values(3, 'Code', '103');
    insert into #t1 values(4, 'ObjectId', 'B');
    insert into #t1 values(4, 'Operation', 'Op4');
    insert into #t1 values(4, 'Code', '104');
    insert into #t1 values(5, 'ObjectId', 'B');
    insert into #t1 values(5, 'Operation', 'Op5');
    insert into #t1 values(5, 'Code', '105');
    -- Create t2
    select 
        rownr,
        Max(case when col = 'ObjectId' then val end) as ObjectId,
        Max(case when col = 'Operation' then val end) as Operation,
        Max(case when col = 'Code' then val end) as Code
        into #t2
    from #t1
    group by rownr
    order by rownr, ObjectId;
    select * from #t2; 
    -- Create #t3    <--- This is where I need help
end

Take note

Please note that I am trying to solve this for Sybase Adaptive Server Anywhere which does not have a pivot statement like Sql Server does, so a solution using a pivot statement will not help me.

Answer 1

You need each set of A, B, etc in a single temporary table with their ObjectIds, with an ordering integer 1,2,3,4 against the ObjectIDs, regardless of the string value of the op.

Getting such an ordered integer is normally easy with an IDENTITY, but you don't want it for all, you want it per A,B,C etc subset.

Thus if you can run a Cursor on each ObjectId value (A,B,C,etc.,) and get the integer ordered operations for those into a temp table, then you can pivot easily with multiple outer joins.

So: create table #l(Id NUMERIC(8) IDENTITY, op VARCHAR(30), obj VARCHAR(300)) go

set identity_insert #l on

Get a cursor on the objectIds and loop like:

select  Id = IDENTITY(8)
    , t2.val op
    , t1.val obj
into 
existing table 
#l
from #t1 t1, #t1 t2
where t1.col = 'ObjectId'
and t1.val = 'A' -- this would be the cursors value
and t1.rownr = t2.rownr
and t2.col = 'Operation'

You will then find that #l can be pivotted nicely with multipler outers, because you'll have a table like:

select * from #l order by 3,1
 Id          op     obj  
 ----------- ------ -----
           1 Op1    A    
           2 Op2    A    
           1 Op3    B    
           2 Op4    B    
           3 Op5    B    

Answer 2

Just in case this helps someone else, here is the code that I eventually came up with to accomplish the required double pivot:

begin
    DECLARE @nr_of_columns smallint;
    DECLARE @qry long varchar;
    DECLARE @i SMALLINT;
    DECLARE @createTable nvarchar(1000);
    create table #t1(rownr int, col nvarchar(15), val nvarchar(300));
    insert into #t1 values(1, 'ObjectId', 'A');
    insert into #t1 values(1, 'Operation', 'Op1');
    insert into #t1 values(1, 'Code', '101');
    insert into #t1 values(2, 'ObjectId', 'A');
    insert into #t1 values(2, 'Operation', 'Op2');
    insert into #t1 values(2, 'Code', '102');
    insert into #t1 values(3, 'ObjectId', 'B');
    insert into #t1 values(3, 'Operation', 'Op3');
    insert into #t1 values(3, 'Code', '103');
    insert into #t1 values(4, 'ObjectId', 'B');
    insert into #t1 values(4, 'Operation', 'Op4');
    insert into #t1 values(4, 'Code', '104');
    insert into #t1 values(5, 'ObjectId', 'B');
    insert into #t1 values(5, 'Operation', 'Op5');
    insert into #t1 values(5, 'Code', '105');
    -- create t2
    select 
        rownr,
        Max(case when col = 'ObjectId' then val end) as ObjectId,
        Max(case when col = 'Operation' then val end) as Operation,
        Max(case when col = 'Code' then val end) as Code
        into #t2
    from #t1
    group by rownr
    order by rownr, ObjectId;
    -- create #t3
  -- Maximum number of column groups in result table
  select max(cols) into @nr_of_columns from (SELECT count() over (partition by ObjectId) as cols from #t2) A;
  -- Create temporary table #t3 to hold results
  SET @i = 1;
  SET @createTable = 'create table #t3(ObjectId nvarchar(300)';
  while @i <= @nr_of_columns loop
    set @createTable = @createTable || ', Operation' || @i || ' nvarchar(300), Code' || @i || ' nvarchar(300)';
    set @i = @i + 1;
  end loop;
  set @createTable = @createTable || ')';
  execute immediate (@createTable);
  -- Pivot into #t3
  for whatever as cur cursor for 
    select 'insert into #t3 select ' || rw as qry from 
    (select '''' || A.ObjectId || ''' AS ObjectId, ' || LIST(attributes) || repeat(',null,null', @nr_of_columns-A.nr_in_group) AS rw from
     (SELECT ObjectId, count() over (partition by ObjectId) nr_in_group, row_number() over (partition by ObjectId order by Operation) nr, ''''||Operation||''' AS Operation' || nr || ',' || '''' || Code || ''' AS Code' || nr  as attributes FROM #t2 order by ObjectId,Operation) A
     group by ObjectId,@nr_of_columns, nr_in_group) B
   DO 
     execute IMMEDIATE (qry);
   end for;
  -- Output #t2
  select * from #t3;
end