Q

Listing data from multiple rows, part 3

Here is the conclusion to Jason's answer. (See part one or part two.)

Option 3 -- Stored Procedures and Functions (in-line and otherwise)

Another option is to write an in-line stored procedure to iterate through a cursor, building your concatenated string as you go. This works, but, depending on how you implement it, you can drastically hurt performance, especially if you are running a separate SQL statement for every row.

If your database platform has support for stored procedures or functions that return result sets, you could write such a stored procedure that returns the entire desired result with one pass through a cursor.

Option 4 -- Scalar Sub-Query Expressions

If you aren't running either of these databases or don't want to rely on "proprietary" SQL or to rely on procedural language, there are still a couple of options.

One option is to use scalar sub-query expressions, like this:

select dpc.PersonInitials, 
       substr(
         (select ', ' || i.Name from PocketContents pc, Items i 
            where pc.ItemID = i.ID 
              and pc.Personinitials = dpc.Personinitials 
              and pc.ItemID = 1) ||
         (select ', ' || i.Name from PocketContents pc, Items i 
            where pc.ItemID = i.ID 
              and pc.Personinitials = dpc.Personinitials 
              and pc.ItemID = 2) ||
         (select ', ' || i.Name from PocketContents pc, Items i 
            where pc.ItemID = i.ID 
              and pc.Personinitials = dpc.Personinitials 
              and pc.ItemID = 3) ||
         (select ', ' || i.Name from PocketContents pc, Items i 
            where pc.ItemID = i.ID 
              and pc.Personinitials = dpc.Personinitials 
              and pc.ItemID = 4) ||
         (select ', ' || i.Name from PocketContents pc, Items i 
            where pc.ItemID = i.ID 
              and pc.Personinitials = dpc.Personinitials 
              and pc.ItemID = 5)
         , 3) as List
  from (select distinct PersonInitials from PocketContents ) dpc

The major drawback to this method is you have to repeat the sub-query for every possible distinct item. As you can imagine, your SQL could get nasty very quickly.

Option 5 -- Crosstabs

Another option is to use cross tabulation to stretch row data into columns. First, we would start with a crosstab table which is basically a matrix that acts as a catalyst. Here is what the crosstab table might look like:

create table CrossTab
( seq number not null primary key,
  seq1 number not null,
  seq2 number not null,
  seq3 number not null,
  seq4 number not null,
  seq5 number not null
);

insert into CrossTab values (1, 1, 0, 0, 0, 0);
insert into CrossTab values (2, 0, 1, 0, 0, 0);
insert into CrossTab values (3, 0, 0, 1, 0, 0);
insert into CrossTab values (4, 0, 0, 0, 1, 0);
insert into CrossTab values (5, 0, 0, 0, 0, 1);
commit;

Such a table is utilitarian, and it wouldn't be a bad idea to keep it around for other similarly creative SQL. You can also build much larger versions of the same as the situation warrants. From here, we can build a SQL statement that looks like this:

select pc.PersonInitials, 
       substr(
         max(substr(', ' || i.Name, 1, seq1 * 20)) || 
         max(substr(', ' || i.Name, 1, seq2 * 20)) || 
         max(substr(', ' || i.Name, 1, seq3 * 20)) || 
         max(substr(', ' || i.Name, 1, seq4 * 20)) ||
         max(substr(', ' || i.Name, 1, seq5 * 20))
       , 3) as List  
  from PocketContents pc, Items i, CrossTab ct
  where pc.ItemID = i.ID
    and pc.ItemID = ct.seq
  group by pc.PersonInitials

As with the previous option, a lot of distinct items to be concatenated can make the SQL statement unwieldy, not to mention slow to execute.


This was first published in January 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close