Now to go the other way…

Prior to Oracle 11gR2, this required a number of creative solutions that can be found on AskTom.oracle.com.

Now you can use a built-in listagg aggreation utility.

e.g. listagg (ename, ‘,’)

where you can pass in the field you wish to build your list with and an additional parameter input to define the delimiter to use.

select
   deptno,
   listagg (ename, ',') WITHIN GROUP (ORDER BY ename)
   enames
from
   emp
group by
   deptno;
 
DEPTNO     ENAMES                                            
---------- --------------------------------------------------
        10 CLARK,KING,MILLER                                 
        20 ADAMS,FORD,JONES,SCOTT,SMITH                      
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD   
 
select
   deptno,
   listagg (ename, '|') WITHIN GROUP (ORDER BY ename)
   enames
from
   emp
group by
   deptno;
DEPTNO     ENAMES                                            
---------- --------------------------------------------------
        10 CLARK|KING|MILLER                                 
        20 ADAMS|FORD|JONES|SCOTT|SMITH                      
        30 ALLEN|BLAKE|JAMES|MARTIN|TURNER|WARD   

P.S. Don’t have 11gR2 ? Here are some other options for you :

http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

http://www.dba-oracle.com/t_converting_rows_columns.htm

This is a nifty little SQL for taking a field with multiple words, and parsing each word into a separate rows based upon spaces. The text in bold is the core SQL of data you wish to work with thus you can add conditions there to filter the data desired.

WITH lines
     AS (    SELECT LEVEL line
               FROM DUAL
         CONNECT BY LEVEL <= 10)
    ,data
     AS (SELECT    ' ' || TRIM (LOWER (columnA)) || ' ' mycolumn
           FROM mytable
          WHERE 1 = 1)
SELECT SUBSTR (mycolumn
              ,  INSTR (mycolumn
                       ,' '
                       ,1
                       ,line)
               + 1
              ,  INSTR (mycolumn
                       ,' '
                       ,1
                       ,line + 1)
               - INSTR (mycolumn
                       ,' '
                       ,1
                       ,line)
               - 1)
          my_column
  FROM data, lines
 WHERE line < LENGTH (mycolumn) - LENGTH (REPLACE (mycolumn, ' ', ''));
 

With a little modification, we can also parse pipe delimited fields into separate rows. Great for transforming a string of values that might contain parameter inputs or values associated to a record stored within a single database field.

WITH lines
     AS (    SELECT LEVEL line
               FROM DUAL
         CONNECT BY LEVEL <= 10)
    ,data
     AS (SELECT    ' '
                || TRIM (
                      LOWER (
                            '|'
                         || columnA
                         || '|'
                         || columnB
                         || '|'))
                || ' '
                   mycolumn
           FROM mytable
          WHERE 1 = 1)
SELECT SUBSTR (mycolumn
              ,  INSTR (mycolumn
                       ,'|'
                       ,1
                       ,line)
               + 1
              ,  INSTR (mycolumn
                       ,'|'
                       ,1
                       ,line + 1)
               - INSTR (mycolumn
                       ,'|'
                       ,1
                       ,line)
               - 1)
          my_column
  FROM data, lines
 WHERE line < LENGTH (mycolumn) - LENGTH (REPLACE (mycolumn, '|', ''));

When working with complex operations and logic, it can be quite difficult understand the flow of data when problems come up. I have built WHO CALLED ME type logic components and they have been very helpful as debugging tools. I simply leverage them with an optional debug switch when I need them to dig deep. I stumbled upon an AskTom posting about a built-in API called OWA_UTIL.who_called_me. An uncovered jewel since 2005 for me. Thought I would share it with you.

AskTom Article

Link: https://support.oracle.mobi

Excerpt from Oracle Announcement :

This first release of My Oracle Support Mobile focuses on the most frequent customer activities, namely viewing and updating Service Requests (SRs). We plan to release additional capabilities in the near future. Please look for future announcements about My Oracle Support Mobile within the “News” section of the My Oracle Support dashboard.

Accessing My Oracle Support Mobile

You will need to be a registered My Oracle Support user to be able to use the My Oracle Support Mobile solution. If you have not already registered for My Oracle Support, you can do so by using the ‘Register Now’ link on the My Oracle Support login page (http://support.oracle.com).

Once you are registered for My Oracle Support, you can access the mobile solution. Initially, you will need to access My Oracle Support Mobile through 
your Web browser on your mobile device by going to http://support.oracle.mobi.

That will bring you to the My Oracle Support Mobile login page. Enter your My Oracle Support user name and password when prompted, and you will be ready to start using the solution.

Using My Oracle Support Mobile

We hope that you find the My Oracle Support Mobile user interface intuitive and easy to use.

When you have connected to My Oracle Support Mobile, you will be able to view SR data that you have access to within the regular My Oracle Support 
solution.

All Open SRs will display a list of the SRs that you have access to and that are still pending resolution. You can drill down on any SR on this list to view more detail and to add updates.

Using the “Favorites” link will display a list of the SRs that you have tagged and update any SR within this list.

The “Requires Your Action” link will display a list of the SRs that are currently awaiting a response from you. You can enter an update to help progress the SR further.

You can also browse for SRs that were created with Support Identifiers contained in your My Oracle Support profile using the Create New Search link, and view and update the SRs that are retrieved. You can save regularly used searches as filters and re-use them in later My Oracle Support Mobile sessions.

Excerpt from a Kata Lesson regarding the creation of code that counts lines of code. It did not necessarily condone the LOC method, but rather the opposite. However this was merely an excercise in solution building to tune development skills.

So whats the reason for this posting? To further exemplify my difficulty in understanding why *any* organization would would want support LOC as a valid method of determining function value metrics. Everything I have read demonstrates that the data derived from the method is pretty much a waste of the effort that goes into analyzing it. By going back to the roots of function value measurement, function points becomes a more legitimate method of measuring and sizing of software. Function points can also be seen cross platform as they are less technology dependent.

With all this said, I can see only one viable environment where LOC is valid: Automated Code Generation. So if the code is auto-generated, then the structure and format is consistent. However, as long as there is a human element involved, the value of LOC begins to degrade rapidly as the human engagement increases. Furthermore, across the organization if we are not able to leverage automated code generators, we lose the ability to get a true picture of cost. Function points again meet this issue head on by allowing us to not only measure across platforms consistently, but also help us identify which technology platform may be better suited to the need. In many cases, we use the tools we know in our area without consideration to other options that could be available within the organization.

Preface to Lines of Code Kata Lesson

Using metrics to measure progress is not inherently bad, but you have to make sure that you measure meaningful things. Measuring business value delivered over time is a good idea (if you can find a way to do it). Measuring lines of code is not. Why? Because on its own, a line of code is not particularly meaningful. If programmer A takes 100 lines of code to achieve the same functionality that takes programmer B 10 lines, is A ten times as productive as B? Quite the opposite.

At a lower level, there will also be fights over what a line of code is. This story from Brian Marick makes the point: [counting code lines] was my very first assignment in my first full time job. One thing I discovered was that our shop had two styles of using braces:

if (foo) {
}

if (foo)
{
}

Users of the former were outraged that the tool saw users of the latter as 50% more productive. That was the beginning of my long non-romance with metrics.