Tuesday, April 2, 2013

How to make each item in stragg list display on separate line



The stragg database function is useful for converting a subquery returning multiple rows into a single column containing a comma separated list of values.

I often use this function to display one of the columns in a row of data.  I recently had to format this so that each stragged item in the list displayed on it's own line.

This is the technique I used

The example subquery below will return a comma separated list of labels for a customer.  I'm only showing this one column selected for brevity but this would be one of many in the select clause.
The code in red was added to append a line feed character "chr(10)" to the beginning of each element in the list.  The substr(val,2) removes it from the 1st element so that it doesn’t begin with a line feed.


SELECT....
(SELECT  substr(stragg(chr(10) || label  ),2)
      FROM customer_label
      WHERE customer_label.account_id  = account.account_id
 ) labels,
 .... 

 FROM ....

 
Line feed characters are normally ignored in html but you can force them to be honored by applying a style of “white-space: pre-line”

add a style of pre to your output text within your table column


   <af:outputText value="#{row.label} styleClass="pre" />


The pre style is defined in SkinBasic.css (or whatever name is your custom skin)

/*** make line feed characters wrap.  For displaying line feed in table column ***/
.pre
{
  white-space: pre-line;
}

You could have also used inline style 
 <af:outputText value="#{row.label} inlineStyle="white-space: pre-line;" />