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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close