Turning Rows into Columns

Question on the forum this week ...


Hi,

I need to build a template like below, in my sql query i have employee details.
i need to display every five employee details in table then 5 in subsequent tables.

so how to split columns..
help me soon.

see below template:
--------------------------------------------------------------------------------------
Description emp1 emp2 emp3 emp4 emp5
--------------------------------------------------------------------------------------
empno 1 2 3 4 5
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
Description emp6 emp7 emp8 emp9 emp10
--------------------------------------------------------------------------------------
empno 6 7 8 9 10
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------


A quick point on questions on the forum, please please please let us know the following:


1. BIP/XMLP version being used
2. What flavor? EBS, PS, JDE, Standalone or using APIs
3. A snippet of XML data if appropriate to the question
4. Template layout - its tough I know but it helps
5. Logs, config files, etc - post the contents of them


Im closing in on 1000 replies and I wonder how of those are 'Hi XXX, Please supply info A, B and C then I can help you. Regards, Tim' format. Lastly, please provide a forum name, its so much better replying 'Hi John' or 'Hi 60 Sausages' - yes we have a Mr. 60 Sausages on the forum ... than replying to User00000 ... who are you?


To the question, were I feeling mean I'd reply, check the user guide, there is enough in there to work out how to do it but it makes for a nice demo so Naresh ... you're lucky :o)

We need to use the @cell command to get the rows of data to display as columns, I assumed we had a data set like this:


<ROWSET>
   <ROW num="1">
      <EMPNO>7810</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>7800</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
 ...
</ROWSET>

All we need to do is loop over each member of the ROW group using the @cell command


<?for-each@cell:ROW?>


this will get us the row values rendering as column values, However the requirement was to show 5 at a time and then break to a new table.
Introducing an XPATH command can achieve this for us:


<?for-each@cell:ROW[position()<6]?>


this means that we will loop over the first 5 ROWs and then stop. You can then introduce a second instance of the template where the XPATH expression presents a range of ROWs to loop over.


<?for-each@cell:ROW[position()>5 and position() <11]?>


this will loop over 6 through 10.


So our template will look something like


RowColumn1:


and generate output like


RowColumn2:


Now, we have hardcoded things here and here are the sample files. But you could quite easily wrap a loop around the table that repeats if for as many rows as is needed based on the incoming data. Im not going to do it just yet ... have a go.

Comments:

Hi there Tim. Thanks a bunch for your postings. I will soon be venturing in to the BIP game and have been keeping an eye on your posts for the last few weeks. Do you think you could put the last 10 or so posts in your RSS feed? :) At the moment the feed only lists the one post on your page. Borkur

Posted by Borkur Steingrimsson on May 24, 2007 at 07:45 PM MDT #

Hi Bokur

http://feeds.feedburner.com/OracleBiPublisherBlog - should give you more than one ... I can see the last 13

Tim

Posted by Tim Dexter on May 25, 2007 at 04:30 AM MDT #

Tim, this is odd. I tried Netvibes.com, Thunderbird as well as Firefox and I still just see your one last blog entry. Must be the Belgian authorities filtering out the rest :) Borkur

Posted by Borkur Steingrimsson on May 28, 2007 at 08:06 AM MDT #

Hi Borkur

OK, dont be so paranoid :o)

I have now fixed this after wading through the settings pages on theblog. You should now be getting the last 10 entries.

Regards, Tim

Posted by Tim Dexter on May 29, 2007 at 04:09 AM MDT #

discern nearby you lap up much for your gratuitys on getting people to primaryty dissects on our contention/blog. Do you read over there can be some active or usdislike elevate ions tos why people do not confirm out inspects as common-sensely?

Posted by Robephvv on February 17, 2009 at 12:14 AM MST #

Hi, I have a requirement wherein i have to implement the same(Converting several rows to a single row) using For-each@cell:ROW syntax in Excel template. Please provide the syntax for doing the same as mentioned in this blog in Excel template Thanks Swathi

Posted by Swathi on July 27, 2010 at 06:21 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today