pivot/crosstab in DB2

[lespaul]

Is there no easy way to create a pivot table in DB2? I saw a few posts where a case statement was used, but those all dealt with a fixed number of columns. My data has a variable number of columns to convert.

We have jurors that come to court and we record the time in and time out. The groups could be called in every day of the term or only 1/2 of the days in the term. I need to create a pivot table that displays the date at the top of the column and each jurors times for that day below:

Juror      2/22/06      2/24/06     2/27/06     3/1/06   
Jane Doe   9am - 10am  11am - 4pm             9am - 12pm
Bob Smith              10am - 6pm   9am - 3pm 
John Adams                                    10am - 6pm

any ideas for dealing with the variable days?

the table is structured like:

JURNUM   SERVDAT   TIMETYPE   TIMEIN   TIMEOUT
12345    20060222  ORIENT     900      1000
12345    20060224  DLYPNL     1100     1600
12345    20060301  DLYPNL     900      1200
65432    20060224  DLYPNL     1000     1800
65432    20060227  JUROR      900      1500
54321    20060227  DLYPNL     1000     1800

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ddiamond]

The easiest way to do this is to link your table to MS Access and then use MS Access's crosstab wizard. I do not believe DB2 has native support for crosstab queries. If you don't want to use Access, then you may need to write a program or stored procedure to do this.

 

[lespaul]

that was what I was afraid of. Thanks anyway!

les

 

[ddiamond]

You can also do it using crystal reports. See thread767-1113813.

 

[lespaul]

don't have access to crystal reports here.

I've got a solution, not one I'm really happy with, but it will work.

thanks anyway!

les

 

[BrianTyler]

I have not completed / checked / tested this but it may be a pointer.

The temporary table is a list of all active dates in the table. Each date is given a sequence number.

The service is matches against this list of dates, and the time enterd in the column corresponding to the date's row number.


select
JURNUM
,case when jury_rownumber = 01 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 02 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 03 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 04 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 05 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 06 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 07 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 08 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 09 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 10 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 11 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 12 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 13 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 14 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 15 then char(timein)||' - '|| char(timeout) else ' ' end
,case when jury_rownumber = 16 then char(timein)||' - '|| char(timeout) else ' ' end
from
juries
,(select cast(rownumber() over (order by b.ch_cl_ref_no, b.ch_stamp) as integer)
as jury_rownumber, distinct sevdate as DATE1 from juries) as T1
where
DATE1 = sevdate
order by
jurnum

 

[lespaul]

that's impressive, but it bombs! Doesn't like the OVER command. I figured out a non-SQL way to get what I need and the user is happy.

thanks!

leslie

 

[blom0344]

I bet someone is going to open this thread in the future and think:
" What was the non-SQL solution leslie cooked up"

Would you share your semi-solution? I am interested...

Ties Blom

 

[lespaul]

I was trying to reduce the number of times the database would be accessed by my Delphi program and thought with a cross tab query I could run a single query and get all the info I needed. I ended up running three queries and process them in delphi to get them in the format and order that I needed.

get the user to input the date needed:

procedure TfrmMain.TimeReport1Click(Sender: TObject);
var
strDate : string;
begin
  strDate := InputBox('Time Report','Enter Date of Term to Process', '');
  ProcessTermVerification(strDate);
end;

find all the distinct groups that served during that time period:

procedure TfrmMain.ProcessTermVerification(ProcessDate : string);
begin
  with dmJMS.qryVerifyPanels do
  begin
    SQL.Clear;
    SQL.Add('SELECT DISTINCT SUBSTRING(PANELID, 1, 9) As GROUPID FROM JMPMAIN WHERE ' +
    'TERMDATE = ' + QuotedStr(convertdateback(ProcessDate)) + ' AND PANELID <> '''' ORDER BY 1');
    Active := True;
    if not isempty then
    begin
      while not eof do
      begin
        GetVerificationInfo(ProcessDate, FieldByname('GROUPID').AsString);
        Next;
      end;
    end
    else
      ShowMessage('No Groups found for the term starting ' + ProcessDate);
  end;
end;

for each group found, print the verification:

procedure TfrmMain.GetVerificationInfo(ProcessDate : string; GroupID : string);
var
DateList : TStrings;
begin
  DateList := TStringList.Create;
  with dmJMS.qryVerifyDates do
  begin
    SQL.Clear;
    SQL.Add('SELECT DISTINCT SERVDAT FROM JMPNEWHOUR WHERE JURNUM IN ' +
    '(SELECT JURNUM FROM JMPMAIN WHERE TERMDATE = ' +
    QuotedStr(convertdateback(ProcessDate)) + ' AND PANELID LIKE ''' + GroupID +
    '%'') ORDER BY SERVDAT');
    Active := True;
    //add all dates to TStringList for index value for printing and assigning times
    while not eof do
    begin
      DateList.Add(FieldByName('SERVDAT').AsString);
      Next;
    end;
  end;

  with dmJMS.qryVerifyAddress do
  begin
    SQL.Clear;
    SQL.Add('SELECT JMPMAIN.JURNUM, PANELID, FIRSTNAME, LASTNAME, STREET1, STREET2, ' +
    'CITY, STATE, ZIPCODE, RTM, REGTIMEIN, REGTIMEOUT FROM JMPMAIN LEFT OUTER JOIN ' +
    'JMPPEMPLE ON JMPMAIN.JURNUM = JMPPEMPLE.JURNUM WHERE PANELID LIKE ''' +
    GroupID + '%'' ORDER BY PANELID');
    Active := True;
  end;

  PrintVerification(DateList, dmJMS.qryVerifyAddress);
end;

In the PrintVerification Process I run another query that gets the hours for each person in the group, however I'm not going to post that process because it's mostly TPrinter commands to output the gathered information.

Since I'm using a TPrinter object the process is still really quick, especially since the process it replaced opened Excel in the background, exported the needed data to a worksheet, ran a macro to calculate the date titles and fill in the hours information.

This process that was replaced could sometimes take 3 - 4 minutes to complete. The new process is done and printed within 30 seconds.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[blom0344]

Well, I'm impressed!

Ties Blom

 

[MarcLodge]

I happened to notice in the SQL forum that there's a similar question which, for interest, may be worth looking at. I don't think the solution would work in this case.

Also, for interest, I seem to recall that in QMF there is an ACROSS parameter in the FORMS output design that does something similar.

Once again, not of use in this case, as it is a different platform, but useful if anybody looks back at this thread in months/years to come.

Marc

 

[MarcLodge]

I must remember to read a post before I send it!!

And the thread referred to in the above post is thread220-1205022